MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Hide Blank Rows

Posted by Alexis on December 07, 2001 11:31 AM

I want to write a macro that will hide all blank rows but I want it to stop when it gets to the last row containing data without knowing what that row will be, since it changes. Any ideas?


Posted by Hansoh on December 07, 2001 12:01 PM

write the macro so that it looks for blank rows from the bottom up, not top down.


Posted by Tom Urtis on December 07, 2001 12:03 PM

Here's one way to do it


This macro assumes:

(1) Your range always starts in A2.
(2) Your range columns are from A thru D.
(3) You would only want to hide rows where no data exists in any of the 4 possible cells (columns A thru D in this example) on a given row.
(4) The last row that contains data will always be populated in column A.

Sub HideRows()
Application.ScreenUpdating = False
Dim i As Integer
Dim RStart As Range
Dim REnd As Range
Set RStart = Range("A2")
Set REnd = Sheets("YourSheetName").Range("A65536").End(xlUp).Offset(0, 3)
Range(RStart, REnd).Select
On Error Resume Next
With Selection
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.CountBlank(.Rows(i)) = 4 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
Set RStart = Nothing
Set REnd = Nothing
Application.ScreenUpdating = True
End Sub


Tom Urtis

Posted by Alexis on December 07, 2001 12:59 PM

Would you be able to help me with the code for that? Thanks

Posted by JACK on December 07, 2001 3:32 PM

I dont see the problem Toms code is perfect is hide rows, simple as taht, OK 4 variables but i did 1000 rows random in 3 seconds... not bad???

I would run with Tom 100% well done Tom

Posted by Ivan F Moala on December 08, 2001 12:53 AM

if you want to hide blamk rows in a column then
here is another way;
1) assumes column A ONLY

Sub hideblankrowsincol()
Dim myRg As Range

'Change the [A1], [A65536] to your range
Set myRg = Range([A1].End(xlDown), [A65536].End(xlUp))

On Error Resume Next
Set myRg = myRg.SpecialCells(4)
If Err = 0 Then
myRg.EntireRow.Hidden = True
MsgBox "No blanks"
End If
Set myRg = Nothing
End Sub