• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for hiding rows very very slow

B

bnpdkh

Member
I have a macro that hides all the blank rows in a range except for three. This is very, almost quicker to do it manually. I have seen multiple ways to hide rows but not much in the way of leaving three blanks still visible. The workbook basically loads information for each day of the week as indicated by the code below. The macro is then run to hide blank rows. You can see that there are three Header rows for each day of the week that are not hidden. This works well but is extremely slow for some reason



Code: 
Sub Hide_Row_1()
 
Dim irow As Long
Application.ScreenUpdating = False
 
 
'Monday hide blank rows
For irow = 9 To 73
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
'Tuesday hide blank rows
For irow = 74 To 76
    If Cells(irow, 2) = Empty Then Rows(irow).Hidden = False
Next irow
For irow = 77 To 141
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
'Wednesday hide blank rows
For irow = 142 To 144
    If Cells(irow, 2) = Empty Then Rows(irow).Hidden = False
Next irow
For irow = 145 To 174
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
'Thursday hide blank rows
For irow = 175 To 177
    If Cells(irow, 2) = Empty Then Rows(irow).Hidden = False
Next irow
For irow = 178 To 207
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
'Friday hide blank rows
For irow = 208 To 210
    If Cells(irow, 2) = Empty Then Rows(irow).Hidden = False
Next irow
For irow = 211 To 240
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
'Saturday hide blank rows
For irow = 241 To 243
    If Cells(irow, 2) = Empty Then Rows(irow).Hidden = False
Next irow
For irow = 244 To 273
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
'Sunday hide blank rows
For irow = 274 To 276
    If Cells(irow, 2) = Empty Then Rows(irow).Hidden = False
Next irow
For irow = 277 To 306
    If Cells(irow, 2) = Empty Then Rows(irow + 3).Hidden = True
Next irow
 
Application.ScreenUpdating = True
End Sub
 
Code runs instantly on my machine. Do you have a lot of volatile formulas perhaps that are getting recalculated everytime you filter/hide rows? If that's the case, then by hiding each row 1 at a time, this code would run very slowly, but it's due to outside influeces, not the macro itself.

Need to either figure out better ways to write the formulas slowing things down, or figure out a way to hide all rows in a single command, rather than multiple lines. Any chance you can share the workbook?
 
To add to what Luke said if you remove the looping construct and set up a helper column (M) the following should replace your coding.

Code: 
Sub HideBlank()
  [M8:M306].AutoFilter 1, "=", , , 0
End Sub

I have replicated your structure in the attached. If I didn't get the formula perfect just change to suit. The theory holds. The speed difference will be noticeable.

Take care

Smallman
 

Attachments

  • Hide1.xlsm
    24.3 KB · Views: 13
Could you, explain how this works, i do not understand what this is doing. I have tried this but it is not working and I am not able to unhide the rows that this macro hid.
 
Do you have any Worksheet_Change events in your workbook/worksheet?

You may want to add an
Application.EnableEvents = False
at the start of the code

and a
Application.EnableEvents = True
at the end

They won't hurt even if you don't have those events
 
bnpdkh said:
Could you, explain how this works, i do not understand what this is doing. I have tried this but it is not working and I am not able to unhide the rows that this macro hid.
Click to expand...
Smallman's code activates the AutoFilter on col M, but the drop down arrow is hidden. If you want to unhide the Rows, go to Data - Sort & Filter, and click either the "Clear" or the "Filter" button to remove the filter.
 
Ok thanks Luke, not really functioning the same as original. Hui, I do have workbook change events associated and I tried the enable events false but it did not make much of a change to run time. Is there a quick way to unhide the next blank row in a range?
 
I guess the issue is the blank rows, what i am getting at is there a way to use a simple hide macro and add some code to unhide or leave the first blank row unhidden. I turned off the screen updating and watched this grind through each line. The code below works fast but does not leave a blank. Not able to share workbook.

Code: 
Dim cell As Range
    For Each cell In Range("B9:B73")
        If cell.Value = "" Then
            cell.EntireRow.Hidden = True
        End If           
    Next
 
Hi ,

Can I suggest something ?

Stop posting code which does not work ; give a clear and comprehensive explanation of what you want done , with specific cell / row / column / worksheet tab references , and you will have your code in less than an hour.

Narayan
 
My apologies, and to clarify the code that has been posted does work, it just runs very slow. The second piece of code also works but does not leave the blanks i am looking for. I am new to VBA and I post code so more experianced people can review and hopefully point out mistakes I have made or give direction on better ways to achieve the desired result. I am looking to learn and have had some great direction provided by people in this forum that has helped me correct other issues I have come up against. Just trying to become "awsome". Please have a look at the first code posted, it works well but runs very slowly. The ranges and cell references are self explanitory
 
Hi brnpdkh,

Understand and appreciate the desire to improve your code. However, in this case, we can't fully help you. The code itself is not the cause of the macro taking a long time to run, there are outside influences. However, we don't know what those are as we don't have the file in front of us. In my first response, I took a guess as what might be causing the issue (lots of volatile formulas) to which Smallman followed up with a way to make the hiding faster, by doing it in one shot.

Your first response then indicated that the code was working, but that you couldn't unhide. Then you said that it doesn't function the same as original (but didn't explain further what that meant).

Per Narayan's comment, if instead of us trying to guess what the layout looks like and what's causing the current code to run slow, you showed us the layout (the specific cells/rows he was talking about) we could probably write a new code fairly quickly.

As it is now, we are just guessing blindly. :(
 
Thanks Luke, appreciate the explanation. I believe you are right about the formulas, the sheet has alot going on. The hide rows is just slow, I can live with it. My apologies to all that replied to this thread if i sounded at all frustrated or unappreciative as that was not my intention. Every person that has responded to this thread has provided help and direction on not only this issue but many others as well. This forum has been a lifsaver. lets consider this one a dead issue and move on to the next. :)
 
You must log in or register to reply here.
Back
Top