Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |