VBA范围对象限制

Question

我正在尝试将多个非相邻单元格从另一个工作簿复制到当前工作簿。

但是经过几次跟踪和错误之后，我发现我最多只能复制64个单元格。如果我再复制一个单元格，我收到一个错误1004:对象'_Worksheet'的方法'Range'失败。

以下是实际代码

For Each cel In wsTarget.Range(data_range(wsSource.Range("rng_tbl_cell_ref_ttl_rows"), wsSource.Range("rng_tbl_cell_ref_st_row"), wsSource.Range("rng_CV_Col_No_A1"), wsSource.Range("rng_cell_ref_sheet")))

数据范围返回"D10,D11,H25,L25,Q25,H26,L26, H24,L24,Q24,G27,H27,L27,Q27,G28,H28,L28, L28, E31,N31,Q31,C36,G36,H40,H41,H42,H43,H44,H45,H46,Q40,Q41,Q42,H51,H52,H53,Q51,Q52,Q53,N54,Q54,Q55,Q56,H57,H58,B62,B63,B64,B65,B66,B67,B68,L62,L63,L64,L65, L67,L68,Q62,Q63,Q64,Q65,Q66,Q67, q68，共67个单元格。

但是总是显示错误1004。当我按如下方式进行测试时(手动添加64个单元格)，它工作得很好。

For Each cel In wsTarget.Range("D10,D11,H25,L25,Q25,H26,L26,Q26,H24,L24,Q24,G27,H27,L27,Q27,G28,H28,L28,Q28,E31,N31,Q31,C36,G36,H40,H41,H42,H43,H44,H45,H46,Q40,Q41,Q42,H51,H52,H53,Q51,Q52,Q53,N54,Q54,Q55,Q56,H57,H58,B62,B63,B64,B65,B66,B67,B68,L62,L63,L64,L65,L66,L67,L68,Q62,Q63,Q64,Q65")

但是如果我添加Q66或任何其他单元格，它显示错误1004。请澄清错误，因为我将来可能需要超过67个单元格。

以下是data_range函数代码。

Function data_range(row_count As Integer, offset_row As Integer, col As Integer, w_sheet_name As String) As String
Dim i As Integer
Dim data_range_string As String
data_range_string = ""
If row_count > 1 Then
data_range_string = ThisWorkbook.Worksheets(w_sheet_name).Cells(offset_row, col)
For i = 1 To row_count - 1
data_range_string = data_range_string & "," & ThisWorkbook.Worksheets(w_sheet_name).Cells(offset_row + i, col)
Next i
ElseIf row_count = 1 Then
data_range_string = ThisWorkbook.Worksheets(w_sheet_name).Cells(1 + offset_row, col)
End If

data_range = data_range_string
End Function

Answer 1

传递给Range()的字符串长度不能超过>255个字符。

快速修复:

Dim rng As Range

Set rng = Application.Union( _
Range("A100,A101,D10,D11,H25,L25,Q25,H26,L26,Q26,H24,L24,Q24,G27,H27,L27,Q27,G28,H28,L28"), _
Range("Q28,E31,N31,Q31,C36,G36,H40,H41,H42,H43,H44,H45,H46,Q40,Q41,Q42,H51,H52,H53,Q51,Q52"), _
Range("Q53,N54,Q54,Q55,Q56,H57,H58,B62,B63,B64,B65,B66,B67,B68,L62,L63,L64,L65,L66,L67,L68,Q62,Q63,Q64,Q65"))