run time error 424
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 |
run time error 424
I transported the macro to a new module and it works, but I wonder why you
can't dim rng as Range? thanks, "Janis" wrote: 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 |
run time error 424
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. Janis wrote: 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 -- Dave Peterson |
run time error 424
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 -- HTH... Jim Thomlinson "Dave Peterson" wrote: 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. Janis wrote: 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 -- Dave Peterson |
All times are GMT +1. The time now is 05:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com