Solved: Find then Hide rows where Col 1 cell is colored & empty or Col 3 cell matches a value

[frank_m]

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
[vba]
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
[/vba]

[Kenneth Hobs]

The function that I wrote in this thread might help you find your ranges. Note that you can hide the entire found rows rather than delete them as shown in the Test example.

http://www.vbaexpress.com/forum/showthread.php?t=38802

For colors, are these interior cell colors or formatted colors? It makes a difference.

If you still need help, try posting a very simple example workbook that has the applied filter. Try solving the found issue first.

[frank_m]

HI Kenneth,

I experimented some with the code you linked me to, but ended up making modifications to the function that I had been using before I tried the Find Method, and luckly now got it to process fast.... 20,000 rows x 30 columns, and 300 rows to hide, all within 1 second (on an old slow computer)

Thanks for your time, as even though I'm ended up not using Find for this, the info you supplied is helping me to better understand it.
[vba]
' other code

'Filter to show only rows where the Col A cell is Blank
rngCell.AutoFilter Field:=1, Criteria1:="="

End If

x = Sum_Visible_Cells(rngCol)

LValue = "Receivables Total " & Format(x, "Currency")

MsgBox LValue

End With

' I set these to False and Manual at the begining of the main macro
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Private Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Double

For Each aCell In Cells_To_Sum
If aCell.Rows.Hidden = False Then
If aCell.Columns.Hidden = False _
And aCell.EntireRow.Cells(1).Interior.ColorIndex = xlNone _
And UCase(aCell.EntireRow.Cells(3).Value) <> "VU" Then
'sum visible cells where the Col 1 cell is not colored, and the Col 3 cell <> "VU"
total = total + aCell.Value
Else
'Hide Rows where the Col 1 Cell is colored and Col 3 Cell value = "VU"
'hiding above rows for visual reasons. - The summing calculates correctly either way.
aCell.EntireRow.Hidden = True
End If

End If
Next

Sum_Visible_Cells = total

End Function
[/vba]