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]

welcome glad resolved & appreciate feedback

Dave

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. Compare the post 10 code, where I have added the tags for you, to the post 9 code. My signature block below has more details.

I also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)