PDA

View Full Version : [SOLVED] Hide rows based on multiple criteria



Ringhal
04-02-2015, 12:02 AM
Hi all

I am trying to hide rows if the cells in a column match a certain value. There's a number of threads that have a similar question but not the answer I am looking for. Posted below is the code that works but takes a long time to run as it has to look at each cell of about 4000 rows.


Sub HideRows()
Dim cel As Range
Dim LastRow2 As Long

With Sheet2
LastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row

For Each cel In .Range("B2:B" & LastRow2)
If cel = "Apple" Or cel = "Banana" Or cel = "Carrot" Then
cel.EntireRow.Hidden = True
Else
'cel.EntireRow.Hidden = False
End If
Next cel
End With

End Sub

ValerieT
04-02-2015, 02:03 AM
May be you can first make a check starting from the bottom. As soon as you find your value, it gives you the "lastRow" to process

Ringhal
04-02-2015, 02:59 AM
Thanks for the suggestion, but more often than not the data I'm looking for (the row I want to hide) is on the last row of the table.

mancubus
04-02-2015, 04:22 AM
what ValerieT suggests is:



With Sheet2
For i = .Cells(.Rows.Count, "B").End(xlUp).Row To 2 Step -1
.Rows(i).Hidden = (.Cells(i, 2).Value = "A" Or .Cells(i, 2).Value = "B" Or .Cells(i, 2).Value = "C")
Next i
End With

mancubus
04-02-2015, 04:23 AM
and below may improve code execution time:



Sub HideRows()

Dim i As Long, calc As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With

With Sheet2
For i = .Cells(.Rows.Count, "B").End(xlUp).Row To 2 Step -1
.Rows(i).Hidden = (.Cells(i, 2).Value = "A" Or .Cells(i, 2).Value = "B" Or .Cells(i, 2).Value = "C")
Next i
End With

With Application
.EnableEvents = True
.Calculation = calc
End With

End Sub

Ringhal
04-02-2015, 07:06 AM
Thanks mancubus,

The code does as expected and execution time is much faster. However, and I didn't mention it in the original post, I use other filters on the same table to hide rows at the same time, but these don't work together (one filter unhides the other filter). It worked previously, with my original code, as I didn't unhide anything.

Adding the below reduced my execution time by about 5 seconds. Originally, I was getting close to 20 seconds runtime.


With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With