Following code used to loop through all rows in a range and hide them based on cell value in that cell, and one below. If both cell values = "" then intent is to hide entire row. All works fine but is terribly slow. Any advice on something faster would be greatly appreciated.
Sheets("Morning Report Export Sheet").Activate
For x = 10 To 108
If Cells(x, 9).Value = "" Then
If Cells(x + 1, 9).Value = "" Then
Cells(x, 9).EntireRow.Hidden = True
End If
End If
Next
... Have also tried the following, but just as slow...
If Cells(x, 9).Value = "" And Cells(x + 1, 9).Value = "" Then
I wrote up both versions because I am bored.
This is the array method mentioned by @MathieuGuindon
Sub HideRowsUsingArrays()
Dim x As Long, HideRows As Range
Dim StartRow As Long, EndRow As Long, Col As Long
'TARGET RANGE
Col = 9
StartRow = 10
EndRow = 108
'TARGET RANGE
Dim sh As Worksheet
Set sh = Sheets("Morning Report Export Sheet")
Dim vArr() As Variant
'Saving all values in the target range to an array
vArr = sh.Cells(StartRow, Col).Resize(EndRow).Value
'Looping through the array
For x = LBound(vArr) To UBound(vArr) - 1
'If val or next val is empty
If vArr(x) = "" And vArr(x + 1) = "" Then
'Add the corresponding row to HideRows range
'Union causes an error if HideRows is nothing, so the first iteration cant use Union
If HideRows Is Nothing Then
Set HideRows = sh.Rows(x + StartRow - 1).EntireRow
Else
Set HideRows = Union(HideRows, sh.Rows(x + StartRow - 1).EntireRow)
End If
End If
Next x
'Hide the gathered rows
If Not HideRows Is Nothing Then HideRows.EntireRow.Hidden = True
End Sub
This is the Range method linked to by @TimWilliams
Sub HideRowsUsingRanges()
Dim cell As Range, HideRows As Range
Dim StartRow As Long, EndRow As Long, Col As Long
'TARGET RANGE
Col = 9
StartRow = 10
EndRow = 108
'TARGET RANGE
Dim sh As Worksheet
Set sh = Sheets("Morning Report Export Sheet")
Dim r As Range
'Saving the target range
set r = sh.Cells(StartRow, Col).Resize(EndRow)
'Looping through each cell of the range
For Each cell In r
'If val or next val is empty
If cell.Value = "" And cell.Offset(1).Value = "" Then
'Add the corresponding row to HideRows range
'Union causes an error if HideRows is nothing, so the first iteration cant use Union
If HideRows Is Nothing Then
Set HideRows = cell
Else
Set HideRows = Union(HideRows, cell)
End If
End If
Next cell
'Hide the gathered rows
If Not HideRows Is Nothing Then HideRows.EntireRow.Hidden = True
End Sub