macro to hide empty rows

[marjattanb]

Hello,

I am trying to hide empty rows in a named range (A5:BU1111) where all
"empty" rows however do have a formula in several columns (where the value is
"").

The only way I have managed is to create a dummy column (which I have named
"ROW_SHRINK_AREA", which creates some values which indicate that the row in
question is not empty. Then I use the macro:

Sub MakeEmptyRowsGoAway()
Application.Goto Reference:="ROW_SHRINK_COLUMN"
For Each R In Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells
varValue = R.Value
If IsNumeric(varValue) Then
If varValue = 0 Then
R.EntireRow.Hidden = True
End If
End If
Next R
End Sub

Would someone please advise me a faster method?

 

[Don Guillett]

You may desire to use a macro that looks at the number of hits

Sub hideemptyrows()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True
Next i
End Sub

 

[Rick Rothstein]

Give this macro a try (without the dummy column) and see if it does what you
want...

Sub HideEmptyRows()
Dim R As Range
For Each R In Range("A5:BU1111").Rows
If WorksheetFunction.CountA(R) = R.Count Then R.Hidden = True
Next
End Sub

 

[Rick Rothstein]

I don't get that macro to work if there are cells containing formulas that
evaluate to the empty string.

 

[Rick Rothstein]

Ignore my code... it does not do what you want.

 

[Per Jessen]

Hi

As you don't tell what the value in ROW_SHRINK_AREA is, I assume the formula
return a blank if the entire row is empty. If your formula return 0 for an
empty row, edit your formula like this:

=If("Your formula" =0,"", "Your formula")

Then you can use either of the suggestions below.

Sub MakeEmptyRowsGoAway()

Dim TargetCol As Range
Set TargetCol = Range("ROW_SHRINK_COLUMN").SpecialCells(xlCellTypeBlanks)
TargetCol.EntireRow.Hidden = True

'OR

With Range("ROW_SHRINK_COLUMN")
.AutoFilter Field:=1, Criteria1:="="
Set TargetCol = .SpecialCells(xlCellTypeVisible)
.AutoFilter
End With
TargetCol.EntireRow.Hidden = True
End Sub

Regards,
Per

 

[Rick Rothstein]

Okay, I am pretty sure this macro does what you want...

Sub HideEmptyRows()
Dim R As Range
Dim SearchRange As Range
Dim LastRow As Long
Dim ColCellCount As Long
Set SearchRange = Range("A5:BU1111")
ColCellCount = SearchRange.Columns.Count
LastRow = SearchRange.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For Each R In SearchRange.Resize(LastRow - SearchRange(1).Row).Rows
If WorksheetFunction.CountBlank(R) = ColCellCount Then R.Hidden = True
Next
End Sub

 

[Don Guillett]

Try this

Sub hideemptyrows()
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows,
xlPrevious).Row
For i = 1 To lr
lc = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lc
If Application.CountA(Rows(i)) < 1 Or _
Cells(i, j).HasFormula And Cells(i, j) = "" Then
Rows(i).Hidden = True
End If
Next j
Next i
End Sub

 

[marjattanb]

Thanks - but I still seem to be stuck. I made it to look like this:

Sub hideemptyrows()
Application.Goto Reference:="ROW_SHRINK_AREA"
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True
Next i
End Sub

- thinking that it would look for zeroes within my dummy column and
consequently hide those rows entirely. Still nothing happens - in addition of
the area being selected.

Anything I do wrong? - sure there is!

Thanking again
Sub hideemptyrows()
Application.Goto Reference:="ROW_SHRINK_AREA"
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True
Next i
End Sub

 

[AltaEgo]

You need to pick up i from the first row in your ROW_SHRINK_AREA. Try

For i = ActiveCell.Row To...

instead of

For i = 1 To ...

 

[marjattanb]

Hi,

thanks again (do u ever sleep??)

Now it gets stuck with SYNTAX ERROR with this:
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows,
xlPrevious).Row

And I am stuck again..

 

[Jacob Skaria]

Try this. You can remove the code between blank rows and (zero + blank)
rows..to suit your requirement

Sub Hideemptyrows()
For lngRow = 1 To Cells(Rows.Count, 1).End(xlUp).Row
'Hide rows with zeros and blanks
If WorksheetFunction.CountIf(Rows(lngRow), 0) + _
WorksheetFunction.CountBlank(Rows(lngRow)) = _
Columns.Count Then Rows(lngRow).Hidden = True

'Hide rows with blanks
'If WorksheetFunction.CountBlank(Rows(1)) = Columns.Count _
'Then Rows(lngRow).Hidden = True

Next
End Sub

 

[keiji kounoike]

If your "ROW_SHRINK_AREA" and Range("A5:BU1111") have no intersections,
then try this one.

Sub testgoawayEmpty()
Dim i As Long
Application.ScreenUpdating = False
For i = 5 To 1111
If Application.CountA(Range(Cells(i, "A"), Cells(i, "BU"))) = 0 Then
Rows(i).Hidden = True
End If
Application.StatusBar = "Now is in Rows(" & i & " )"
Next
End Sub

Keiji

 

[Rick Rothstein]

Hmm! I see I put my "ignore this code" message on the wrong sub-thread. This
was the code you were supposed to ignore because it didn't work; HOWEVER, do
try my later posted code as that does work.

 

[Don Guillett]

Did you notice a different color??? Below is ONE line. So either use the
delete key to bring up the second part or put in a line continuation
character which is a space and underscore. xlByRows _


lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows,
xlPrevious).Row