using a macro to hide empty rows

BSGP8NTBALL

New Member
Joined
Nov 23, 2005
Messages
14
I have turned into the company excel guy and I am very limited in my macro knowledge. I have already signed up for some classes but they don't begin until after the holidays. I want to hide a row if there is no data inputed in that row. I have searched and read other macro's that seem to do close to the same thing. Is this possible? Can someone help to point me to where I could find an example of this macro or is it easy enough for some to just "whip" it up in a few lines? Thanks ahead for your time/
 
Try:

EndNum = Range("AZ65536").End(xlUp).Row

Previously you were assigning it the number of rows from row 23 to the end.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
BSGP8NTBALL said:
Also, is this autofilter macro deleting the rows or hidding them?

Oop. As it was written, it would delete the rows. Sorry. I kind of forgot what you were going for. :oops:

Though really, you could just turn on autofilter and then not bother with any code at all--just set a custom filter on column AZ for "does not equal | 0 "

Though if you *really* want to do it with code, I think this should work. It should basically toggle the autofilter on/off, automatically hiding the "0" rows.

Note: This is assuming that AZ22 would be a "header" row for the data starting in AZ23
Code:
Sub ToggleRows()

'if the autofilter is not on
If ActiveSheet.AutoFilterMode = False Then
    'autofilter column AZ to hide rows with 0
    Range("AZ22").AutoFilter field:=52, Criteria1:="<>0"
Else
    'if autofilter *is* on, turn off autofilter
    Range("AZ22").AutoFilter
End If

End Sub
 
Upvote 0
Well alrightly guys this is what I have:

Sub HideRows()
'

Dim EndNum As Integer

EndNum = Range("AZ65536").End(xlUp).Row


For i = 23 To EndNum
Range("az" & i).Select
If ActiveCell.Value = 0 Then
Selection.EntireRow.Hidden = True

End If
Next i

End Sub

It works exactly like I need it to. Thanks for all the help and time you guys put towards this. I just wanted to thank everyone and post the working finished product. I searched so many threads that just ended, I thought was pretty rude of whoever got what they needed and couldn't at least log back on to say thanks.......
 
Upvote 0
New question, I found out that after the user hides the rows with "0" there is times they need to go back and edit the rows that have been hidden. How can I unhide all rows, is there a show all command or something. I have the first macro linked to a button labled "Hide" and I would like another next to it with "Show all".
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ShowEmWhatYaGot()

Cells.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>
 
Upvote 0
I get "unable to set the hidden property of the range class"

I had searched and tried a few other unhid macros that seemed like they should work but I get the same error message.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top