Object or With Block Error

[5150]

Guys,

I am getting an error Run-time error 91: Object variable or With Block variable not set. Can you guys help me fix the error to make this code work. The code just needs to Delete the visible rows and then clear the existing filter to display the results after the deletion.

I appreciate your help!

Sub DeleteRows()
'


Dim lastrow As Range


Sheets("TEST").Select


lastrow = Range("A" & Rows.Count).End(xlUp).Row



'Delete Visible Rows

Range("A5:O" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete


'Clear Filter


With Sheets("TEST")

  If .FilterMode Then .ShowAllData

End With


End Sub

[judgeh59]

try this

change the DIM statement for lastrow to an integer and try this for the lastrow

lastrow = Cells(999999, 1).End(xlUp).Row

[buran]

Just change Dim lastrow As Long

@judgeh59, even if it's save to assume that lastrow will be less than the max allowed integer number it's bad practice to declare variable that will hold row number as integer and it's a call for future problems - error#6: OverFlow.

[judgeh59]

@buran - yup - I usually set a global variable as long and then use that....because of course 999999 wouldn't work for integers....thanks

[buran]

:-)
@judgeh59: in this case just hope that user will not save the file as xls and face the fact that there are just around 65K rows

[judgeh59]

correct -

[5150]

Works perfect! While playing with it, I noticed that it also worked with Dim lastrow as Variant.

I appreciate your support.

Thanks guys!