Delete Loop Error

  • Thread starter Thread starter LiAD
  • Start date Start date
L

LiAD

Hi,

I was given this code on the forum, (cant find my old post), which works
fine, deleting the grey coloured cells, however once it has deleted the lines
I need it errors on this line

Range("L3:L" & Rows.Count).Find("", SearchFormat:=True).EntireRow.delete

with Run time error 91, object variable or with block variable not set.

The same error appears if there are no grey cells to delete. Am I missing
something from this code?

Thanks
LiAD

Application.FindFormat.Interior.ColorIndex = 15
On Error GoTo Done
Do
Range("L3:L" & Rows.Count).Find("", SearchFormat:=True).EntireRow.delete
xlShiftUp
Loop
Done:
 
to be expected. The line inherently does two things , it sets the range and
then deletes the row. If there's no cells detected, then trying to delete
from a null raises an error

in the following code sample, I've split the line into the two parts so that
if you step (F8) through it, you'll see what happens.
Try it on a blank column, a column with contiguous data and a column with a
break in the data..there's no error, simply that the range isn't set so
remains null and the IF takes care of it.

Sub rubout()
Dim target As Range
Set target = Range("L3:L" & Rows.Count).Find("", SearchFormat:=True)
If Not target Is Nothing Then
target.EntireRow.Delete
End If
End Sub
 
Try the below which will loop until all rows are found...

Application.FindFormat.Interior.ColorIndex = 15
Do
Set varFound = Range("L3:L" & Rows.Count).Find("", SearchFormat:=True)
If Not varFound Is Nothing Then varFound.EntireRow.Delete xlShiftUp
Loop Until varFound Is Nothing

If this post helps click Yes
 
Spot on once again

Thanks!

Jacob Skaria said:
Try the below which will loop until all rows are found...

Application.FindFormat.Interior.ColorIndex = 15
Do
Set varFound = Range("L3:L" & Rows.Count).Find("", SearchFormat:=True)
If Not varFound Is Nothing Then varFound.EntireRow.Delete xlShiftUp
Loop Until varFound Is Nothing

If this post helps click Yes
Click to expand...
 
Back
Top