run time error 424

J

Janis

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
 
J

Janis

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

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.
 
J

Jim Thomlinson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top