Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



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?

Thanks


Check out our Excel VBA Resources

Re: Hide Blank Rows

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.

-h


Here's one way to do it

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

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
Range("A1").Select
Application.ScreenUpdating = True
End Sub


HTH

Tom Urtis


Re: Hide Blank Rows

Posted by Alexis on December 07, 2001 12:59 PM
Would you be able to help me with the code for that? Thanks

Re: Hide Blank Rows

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


Re: Hide Blank Rows - another way

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
Else
MsgBox "No blanks"
End If
Set myRg = Nothing
End Sub

HTH


Ivan




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.