Macro - Hide Empty rows

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Is it possible to create a macro that will hide all rows
in a worksheet that does not contain data or text? If so,
how would it look like.
 
Hi Robert,

The following code will do as you ask. I changed John Walkenbach's
DeleteEmptyRows (available at:
http://www.j-walk.com/ss/excel/tips/tip56.htm) so it would hide instead of
delete:

Sub HideEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).EntireRow.Hidden =
True
Next r
End Sub

tim

| Is it possible to create a macro that will hide all rows
| in a worksheet that does not contain data or text? If so,
| how would it look like.
 
Hi Robert,

Are there formulas in the cells? If so, the code will not work. The rows
must be completely empty.

tim

| Thanks for the information. I may be doing something
| wrong but I do not get an error when I run the macro.
| However, it does not hide any empty rows. Not sure what
| I could be doing wrong.
|
|
| >-----Original Message-----
| >Hi Robert,
| >
| >The following code will do as you ask. I changed John
| Walkenbach's
| >DeleteEmptyRows (available at:
| >http://www.j-walk.com/ss/excel/tips/tip56.htm) so it
| would hide instead of
| >delete:
| >
| >Sub HideEmptyRows()
| > LastRow = ActiveSheet.UsedRange.Row - 1 + _
| > ActiveSheet.UsedRange.Rows.Count
| > Application.ScreenUpdating = False
| > For r = LastRow To 1 Step -1
| > If Application.CountA(Rows(r)) = 0 Then Rows
| (r).EntireRow.Hidden =
| >True
| > Next r
| >End Sub
| >
| >tim
| >
| >| >| Is it possible to create a macro that will hide all
| rows
| >| in a worksheet that does not contain data or text? If
| so,
| >| how would it look like.
| >
| >
| >.
| >
 
The other non-vba option is to use Data / Filter / Autofilter / Custom / Not equal to 0
 
Back
Top