VBA "Error 424" Object Needed

[Dennis]

Using XL 2003

I am using the following code as a work-around for the 8,192 non-contiguous cells
limit in XL.

Dim myDeleteRange As Range
Dim myCounter As Long
For myCounter = 30002 To 2 Step -5000
On Error Resume Next
Set myDeleteRange = Rows(myCounter).Resize(5000).SpecialCells(xlVisible)
On Error GoTo 0
If Not myDeleteRange Is Nothing Then
myDeleteRange.EntireRow.Delete ' <- Error message occurs here
End If
Next

I am getting an "Object Needed" error - I guess I am too close to see it.

TIA Dennis

 

[Dave Peterson]

Once myDeleteRange is set to a range, it isn't getting set to nothing if the
specialcells line fails.

Option Explicit
Sub testme()

Dim myDeleteRange As Range
Dim myCounter As Long
For myCounter = 30002 To 2 Step -5000
Set myDeleteRange = Nothing
On Error Resume Next
Set myDeleteRange = Cells(myCounter,
"A").Resize(5000).SpecialCells(xlVisible)
On Error GoTo 0
If Not myDeleteRange Is Nothing Then
Debug.Print myDeleteRange.Address
myDeleteRange.EntireRow.Delete
End If
Next
End Sub

And unencumbered by the thought process, I would think that if you could pick a
column to inspect, excel would find it easier than using a row.

I changed:
Set myDeleteRange = Rows(myCounter).Resize(5000).SpecialCells(xlVisible)
to
Set myDeleteRange = Cells(myCounter, "A").Resize(5000).SpecialCells(xlVisible)


But if you really need the whole row, then you may exceed the 8192 limit if
you're looking at 5000 rows x 256 columns (and every other column could be
hidden making the limit on the discontiguous range pop up again).