CODEWITHSUNDEEP
excelvba
taezar.tw
taezar.tw

Reputation: 63

VBA Range object limits

I am trying to copy multiple non-adjacent cells from another workbook to current workbook.

But after several trail and errors, I found that I can only copy up to 64 cells. If I copy one more cell, I received an Error 1004: Method 'Range' of object '_Worksheet' failed.

The following one is actual code.

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")))

The data_range return "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,Q67,Q68", total of 67 cells.

But it always shows the Error 1004. When I tested as follow (added 64 cells manually), it works perfectly.

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")

But if I add Q66 or any other cell, it shows Error 1004. Please kindly clarify the error as I may need more than 67 cells in future.

The following is the data_range function codes.

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

Upvotes: 2

Views: 161

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

The string passed to Range() can't be >255 characters in length.

Quick fix:

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"))

Upvotes: 4

Related Questions