CODEWITHSUNDEEP
vbahiderows
Alex Keyes
Alex Keyes

Reputation: 1

Hiding Empty Rows w VBA - Increasing Speed

I'm trying to hide rows where the cell text length is equal to zero. Currently using the following code, but it's extremely slow due to chunking through each line one by one: 

Sub HideRows()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False

Dim cell As Range
For Each cell In Range("B1:B1000")
    If Not IsEmpty(cell) Then
        If Len(cell.Text) = 0 Then
            cell.EntireRow.Hidden = True
        End If
    End If
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

I've seen inother places that you can use 

Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

But because my cells are not blank, just giving you a result of "" from a formula, I don't see this working for me.

Is there any other way to make this process more efficient?

Upvotes: 0

Views: 34

Answers (1)

Tehscript
Tehscript

Reputation: 2556

You can hide all rows in one action like this:

Sub hideRows()
Dim rng As Range
For Each cell In Range("B1:B1000")
    If Len(cell.Text) = 0 Then
        If rng Is Nothing Then
            Set rng = cell
        Else
            Set rng = Union(rng, cell)
        End If
    End If
Next
rng.EntireRow.Hidden = True
End Sub

Upvotes: 1

Related Questions