• 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.

How do I hide blank rows?

bell407mech

New Member
I am having a difficult time trying to come up a way to hide "blank" rows. I don't want to use the auto-filter. I just want it to be an automatic feature. Any input would be appreciated!


(Bear with me, I have learned a lot from this website!)


http://cid-5a3aa6ce1dced7d9.office.live.com/view.aspx/Public/N222222%20COMPONENT%20SHEET.xlsx
 

Hui

Excel Ninja
Staff member
I have assumed you want to hide rows on the Items Due page of your spreadsheet

and that you want to do it when Column B is blank


You should note that your formula is actually putting a Space " " in Column B it isn't blank but thats ok


2 ideas:


Have you tried grouping Rows


Select the Row with the zero data and Alt Shift Right Arrow

Once the row is grouped

Click on the - on the left margin to hide the row

Click on the + to unhide the row

Alt Shift Left Arrow to remove the group

This way you have a visual clue that there are hidden rows


otherwise the following macro will do the job for you


Copy and paste the code to the Code Page for the Items Due page in VBA

You can run it manually or put a button or shape on your page and link it to that

[pre]
Code:
Sub Hide_Rows()

Dim c As Range
Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select
For Each c In Selection
If c.Value = " " or c.Value = "" Then c.EntireRow.Hidden = True
Next

End Sub

The following complementary macro is self explanatory

Sub Unhide_All_Rows()

Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select

Selection.EntireRow.Hidden = False
Range("A1").Select

End Sub
[/pre]
 

bell407mech

New Member
WOW! I can't believe you had it all figured out in matter of minutes! I had been working on this for four weeks!!


LOL! Thanks again!
 
Top