Is there a limit to the amount of cells a range object can hold?

user_10540017 0
Viewed 78

I have a worksheet with roughly 300'000 rows of which a portion needs to be deleted.

I populate an array with row numbers, and perform a delete procedure for the rows in that array:

Sub Start()
'...

b = 0 
ReDim arr(b)

For i = LRow To 2 Step -1
    If .Cells(i, "L").Value = "" then
        ReDim Preserve arr(b)
        arr(b) = i
        b = b + 1
    End If
Next i

.Range("A" & Join(arr, ",A")).EntireRow.Delete

'...
End Sub

With the code above, arr ends up containing some 68'000 row numbers.

On the line that ought to delete these rows, I get the error

Method 'Range' of object '_Worksheet' failed

This also occurs when I try to select these, instead of deleting them.

Taking a portion of the output from the Join function performs as expected, such as:

.Range("A" & "2465,A2457,A2432,A2428,A2410,A2405,A2376,A2372,A2358,A2354").EntireRow.Delete

What causes the code to fail? Is there a limit on the Range object I am unaware of?

vba excel arrays
3 Answers

This is a way:

Sub Start()
    '...

    Dim DeleteRange As Range

    For i = LRow To 2 Step -1
        If .Cells(i, "L").Value = "" Then
            If DeleteRange Is Nothing Then
                Set DeleteRange = .Cells(i, "A")
            Else
                Set DeleteRange = Union(DeleteRange, .Cells(i, "A"))
            End If
        End If
    Next i

    DeleteRange.EntireRow.Delete

    '...

End Sub

Thought trying your way, it should work as you say with:

.Range("A" & "2465,A2457,A2432,A2428,A2410,A2405,A2376,A2372,A2358,A2354").EntireRow.Delete As long as your range doesn't exceed 255 characters. If it doesn't just check what is the value for: "A" & Join(arr, ",A") maybe there is something wrong or missing.

Edit: Another way to do this

Sub Start()
    '...

    Dim arrData
    Dim j As Long

    b = 1

    arr = .UsedRange.Value
    ReDim arrData(1 To UBound(arr), 1 To UBound(arr, 2))
    For i = 1 To UBound(arr)
        If arr(i, 12) <> vbNullString Then
            For j = 1 To UBound(arrData, 2)
                arrData(b, j) = arr(i, j)
            Next j
            b = b + 1
        End If
    Next i

    .UsedRange = arrData

'...
End Sub
user_75586820

There's a 255 character limit for a Range reference, which you've surpassed by leaps and bounds; specifically,

"A" & "2465,A2457,A2432,A2428,A2410,A2405,A2376,A2372,A2358,A2354..."

can only be a max of 255 characters.

This can be validated with a simple test:

Sub Test()
    Dim arr(1 To 40) As String
    Dim arr2(1 To 80) As String

    Dim i As Long

    For i = LBound(arr) To UBound(arr)
        arr(i) = CStr(i)
    Next i

    For i = LBound(arr2) To UBound(arr2)
        arr2(i) = CStr(i)
    Next i

    Debug.Print Len("A" & Join(arr, ",A")) ' returns 150
    Debug.Print Len("A" & Join(arr2, ",A")) ' returns 310

    Range("A" & Join(arr, ",A")).EntireRow.Delete ' works
    Range("A" & Join(arr2, ",A")).EntireRow.Delete ' fails
End Sub

Union or an array solution, as already proposed, are alternatives.

user_92458530

Alternative combining array Approach with Union function

Looping through ranges via VBA is time consuming, deleting via EntireRow using Union is fast, so a combination of both instead of collecting all remaining column values seems to be a good approach:

Sub DeleteEmptyRows()
With ThisWorkbook.Worksheets("Nonsens")
' assign column data to 2-dim array
  Dim v, lrow&: lrow = .Range("L" & Rows.Count).End(xlUp).Row
  v = .Range("L1:L" & lrow)                     ' start from top to get correct row numbers later
' check first empty cell
  Dim currRow&, delRng As Range
  For currRow = 2 To UBound(v)
      If v(currRow, 1) = vbNullString Then
           Set delRng = .Range("A" & currRow)
           Exit For
      End If
  Next currRow
' collect rest via UNION function
  For currRow = currRow To UBound(v)
      If v(currRow, 1) = vbNullString Then Set delRng = Union(delRng, .Range("A" & currRow))
  Next currRow
' delete entire rows in range set
  If Not delRng Is Nothing Then delRng.EntireRow.Delete

End With

End Sub
user_64602970
Related