run time error 424

[Guest]

This stops compiling on if rngToDelete is nothing, presumably becuase it is
not set previously. Am I right rng is a Variant? Are the two following
lines reversed?
Thanks,
if rngToDelete is nothing Then
and
Set rngToDelete



Private Sub delGrayRows()
Dim Rng As Variant
Dim rngToSearch As Variant
Dim rngToDelete As Variant
Const LtGray = 15
Const DkGray = 48

With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
End With
For Each Rng In rngToSearch
If Rng.Interior.ColorIndex = LtGray Or _
Rng.Interior.ColorIndex = DkGray Then
If rngToDelete Is Nothing Then
Set rngToDelete = Rng
Else
Set rngToDelete = Union(rngToDelete, Rng)
End If
End If
Next Rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

End Sub

 

[Guest]

I transported the macro to a new module and it works, but I wonder why you
can't dim rng as Range?
thanks,

 

[Dave Peterson]

Change all 3 of those Dim's to "as Range"

Since Rngtodelete is a variant in your code, it won't be initialized as Nothing.

 

[Guest]

Here it is cleaned up a tad...

Private Sub delGrayRows()
Dim rng As Range
Dim rngToSearch As Range
Dim rngToDelete As Range
Const LtGray As Long = 15
Const DkGray As Long = 48

With ActiveSheet
Set rngToSearch = .Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
End With
For Each rng In rngToSearch
If rng.Interior.ColorIndex = LtGray Or _
rng.Interior.ColorIndex = DkGray Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rngToDelete, rng)
End If
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

End Sub