VBA Code to Hide and Unhide Rows based on dynamically changing data

[leebrockney]

hello all!! I am trying to build a statement creation tool within Excel. the statement has a few different sections that could range anywhere from a few rows to a couple hundred rows. I am using the below code to review the statement and to hide and unhide any rows that are either unused or were hidden but now have data which means they need unhidden. the problem I am having is that this code takes A LONG TIME to run. probably around 2 minutes each time I kick the code off. While this isn't a long time in the bigger scheme of things, the admin who will be using this has to create dozen and dozens of these reports and 2 minutes each is a big deal when duplicated across many statements.

I am just hoping that maybe there is a more efficient code that wouldnt take so long to run. thanks for any feedback you might have!

Sub Button1_Click()
Dim stmntRange As Range

Application.ScreenUpdating = False
For Each stmntRange In Range("E11:E658")
If (stmntRange.Value = "") Or (stmntRange.Value = "0") Then
stmntRange.EntireRow.Hidden = True
Else
stmntRange.EntireRow.Hidden = False
End If
Next stmntRange
Application.ScreenUpdating = True
End Sub

 

[dmt32]

Hi,
see if this update to your code is any improvement

Sub Button1_Click()
    Dim stmntRange  As Range
    Dim hiderows    As Range, rng As Range
    Dim lastrow     As Long
    
    lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("E11").Resize(lastrow)
    
    Application.ScreenUpdating = False
    rng.Rows.Hidden = False
    For Each stmntRange In rng.Cells
        If Val(stmntRange.Value) = 0 Then
            If hiderows Is Nothing Then
                Set hiderows = stmntRange
            Else
                Set hiderows = Union(hiderows, stmntRange)
            End If
        End If
    Next stmntRange
    If Not hiderows Is Nothing Then hiderows.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub

Dave

 

[leebrockney]

Hi,
see if this update to your code is any improvement

Sub Button1_Click()
    Dim stmntRange  As Range
    Dim hiderows    As Range, rng As Range
    Dim lastrow     As Long
   
    lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("E11").Resize(lastrow)
   
    Application.ScreenUpdating = False
    rng.Rows.Hidden = False
    For Each stmntRange In rng.Cells
        If Val(stmntRange.Value) = 0 Then
            If hiderows Is Nothing Then
                Set hiderows = stmntRange
            Else
                Set hiderows = Union(hiderows, stmntRange)
            End If
        End If
    Next stmntRange
    If Not hiderows Is Nothing Then hiderows.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub

Dave

Thanks Dave, I copied your code and ran but it hid all rows from row 11 down to the last row. it left nothing unhidden but it ran very quickly.

 

[dmt32]

sorry, thought I was being helpful making the range dynamic

change these lines

lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("E11").Resize(lastrow)

back to your fixed range

 Set rng = Range("E11:E658")

and see if resolves

Dave

 

[leebrockney]

sorry, thought I was being helpful making the range dynamic

change these lines

lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("E11").Resize(lastrow)

back to your fixed range

 Set rng = Range("E11:E658")

and see if resolves

Dave

I was hoping the dynamic range worked! it was a good idea. unfortunately now the code just hides everything between 11 and 658.

 

[leebrockney]

I was hoping the dynamic range worked! it was a good idea. unfortunately now the code just hides everything between 11 and 658.

I should also mention that the rows are blank, they do not have zeros. they have formulas that are returning a blank.

 

[dmt32]

I should also mention that the rows are blank, they do not have zeros. they have formulas that are returning a blank.

that would have been helpful to know

try changing this line

If Val(stmntRange.Value) = 0 Then

to this

If Len(stmntRange.Value) = 0 Then

Dave

 

[leebrockney]

that would have been helpful to know

try changing this line

If Val(stmntRange.Value) = 0 Then

to this

If Len(stmntRange.Value) = 0 Then

Dave

This works perfect now and it runs almost instantly!! Thank you so much!!

 

[leebrockney]

This works perfect now and it runs almost instantly!! Thank you so much!!

Final Code for others

Sub Button1_Click()
Dim stmntRange As Range
Dim hiderows As Range, rng As Range
Dim lastrow As Long

Set rng = Range("E11:E658")

Application.ScreenUpdating = False
rng.Rows.Hidden = False
For Each stmntRange In rng.Cells
If Len(stmntRange.Value) = 0 Then
If hiderows Is Nothing Then
Set hiderows = stmntRange
Else
Set hiderows = Union(hiderows, stmntRange)
End If
End If
Next stmntRange
If Not hiderows Is Nothing Then hiderows.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

 

[leebrockney]

I also updated back to the dynamic range and it works great. Final code with dynamic range

Sub Button1_Click()
    Dim stmntRange  As Range
    Dim hiderows    As Range, rng As Range
    Dim lastrow     As Long
   
    lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = Range("E11").Resize(lastrow)
   
    Application.ScreenUpdating = False
    rng.Rows.Hidden = False
    For Each stmntRange In rng.Cells
        If Len(stmntRange.Value) = 0 Then
            If hiderows Is Nothing Then
                Set hiderows = stmntRange
            Else
                Set hiderows = Union(hiderows, stmntRange)
            End If
        End If
    Next stmntRange
    If Not hiderows Is Nothing Then hiderows.EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub