frank_m
03-02-2012, 01:16 PM
I'm trying to write code that will Find Col 3 cells that contain the Value "VU", then Hide those Rows
Then Find Col 1 cells that are (Colored and Empty) and Hide those Rows also.
Note that the Sheet has been Filtered before I do this.
Question 1: Is the find method the fastest way to do this ' Edit reworded to show which line is highlighted from the error
Question 2: With the code below I'm getting the Error: Object Varialbe or Block Not Set,
on this line: Loop While Not Found Is Nothing And Found.Address <> FirstAddress
so if the Find method is a good way to go please help me correct that.
Thanks
Dim FirstAddress As String
Dim Found As Range
Dim rng As Range
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Edit moved this to be included inside the With Block
'---------------------- C16:C & LastRow
Set rng = .Range(.Cells(16, 3), .Cells(LastRow, 3))
Set Found = rng.Find("VU", LookIn:=xlValues)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.EntireRow.Hidden = True
Set Found = rng.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
'----------------------- A16:A & LastRow
Set rng = .Range(.Cells(16, 1), .Cells(LastRow, 1))
Set Found = rng.Find("", LookIn:=xlValues)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
If Found.Interior.ColorIndex <> xlNone Then
Found.EntireRow.Hidden = True
End If
Set Found = rng.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Then Find Col 1 cells that are (Colored and Empty) and Hide those Rows also.
Note that the Sheet has been Filtered before I do this.
Question 1: Is the find method the fastest way to do this ' Edit reworded to show which line is highlighted from the error
Question 2: With the code below I'm getting the Error: Object Varialbe or Block Not Set,
on this line: Loop While Not Found Is Nothing And Found.Address <> FirstAddress
so if the Find method is a good way to go please help me correct that.
Thanks
Dim FirstAddress As String
Dim Found As Range
Dim rng As Range
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Edit moved this to be included inside the With Block
'---------------------- C16:C & LastRow
Set rng = .Range(.Cells(16, 3), .Cells(LastRow, 3))
Set Found = rng.Find("VU", LookIn:=xlValues)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.EntireRow.Hidden = True
Set Found = rng.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
'----------------------- A16:A & LastRow
Set rng = .Range(.Cells(16, 1), .Cells(LastRow, 1))
Set Found = rng.Find("", LookIn:=xlValues)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
If Found.Interior.ColorIndex <> xlNone Then
Found.EntireRow.Hidden = True
End If
Set Found = rng.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With