CODEWITHSUNDEEP
vbafor-loophidden
iontom
iontom

Reputation: 473

What is the fastest way to conditionally hide a range of rows in VBA?

I'm trying to speed up my VBA code and am not sure how to do it. The slowest task by far is my loop to hide empty rows. (I have several pivot tables aligned vertically and I had to leave space to allow them to expand when new data is added.)

Public Sub HideRows(list1 As Range)
Dim cell As Range

    For Each cell In list1
        cell.EntireRow.Hidden = (cell.Value = "")
    Next cell
End Sub

Public Sub UnhideRows(list1 As Range)
Dim cell As Range

    For Each cell In list1

        If (cell.Value <> "") Then cell.EntireRow.Hidden = False
    Next cell
End Sub

Upvotes: 1

Views: 8266

Answers (2)

mr.Reband
mr.Reband

Reputation: 2430

No need to loop -- you should be able to hide the entire range all at once -- I tested using this sub: 

Public Sub HideRows_test(list1 As Range)
    list1.EntireRow.Hidden = True
End Sub

edit: sorry, I jumped the gun on this, didn't realize there was a condition on the range. However, instead of hiding each separately, you can build a new range using your existing loop then hide all rows in it afterwards.

edit: I mentioned you can build the range dynamically, then hide all rows at the end -- here's how: 

Public Sub HideRows(list1 As Range)
    Dim cellsToHide As Range
    Set cellsToHide = Nothing

    Dim cell As Range
    For Each cell In list1
        If cell.Value = "" Then
            If cellsToHide Is Nothing Then
                Set cellsToHide = cell
            Else
                Set cellsToHide = Union(cellsToHide, cell)
            End If
        End If
    Next cell
    cellsToHide.EntireRow.Hidden = True

End Sub

However, I think you can indeed get rid of the for each entirely by using SpecialCells(xlCellTypeBlanks), like so: 

Public Sub HideRows(list1 As Range)
    Set list1 = list1.SpecialCells(xlCellTypeBlanks)
    list1.EntireRow.Hidden = True
End Sub

Upvotes: 2

enderland
enderland

Reputation: 14145

Look into using Application.ScreenUpdating. Setting this to false prevents Excel from updating the display after every operation, which increases speed significantly - ESPECIALLY when using a slow command such as hide/unhide.

If you don't need to iterate through your cells, you can do something similar to mr Reband's answer.

But in general, Application.ScreenUpdating = false will speed up nearly all code interacting with Excel significantly.

Public Sub HideRows(list1 As Range)
Dim cell As Range
Application.ScreenUpdating = False
    For Each cell In list1
        cell.EntireRow.Hidden = (cell.Value = "")
    Next cell
Application.ScreenUpdating = True
End Sub

Public Sub UnhideRows(list1 As Range)
Dim cell As Range
Application.ScreenUpdating = False

    For Each cell In list1

        If (cell.Value <> "") Then cell.EntireRow.Hidden = False
    Next cell
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions