hide blank rows

[Guest]

i need to hide all those blank row in my whoel worksheet. found this code in
another thread but realise that this hide the row if that column is blank.
however, what i want is to only hide the row if whole row is w/o data and not
particular column w/o data. so hw can i modify this code or is that any
other code to acheive this?

Sub Tester()
On Error Resume Next
Range("A120").Columns(1).SpecialCells(xlBlanks). _
EntireRow.Hidden = True
On Error GoTo 0

End Sub

 

[Guest]

Hi Violet

Below is not elegant but I think it should work OK, just change your sheet
name and range

Sub Tester()
Dim rng As Range
Dim x As Long
With Worksheets("Sheet4")
Set rng = Worksheets("Sheet4").Range("b2:b20")

For Each c In rng
x = c.Row
If .Range("A" & x).End(xlToRight).Column = 256 Then
.Range("A" & x).EntireRow.Hidden = True
End If

Next
End With

End Sub

 

[dodgo]

You can do it without code by creating a column that concatenates all
other columns, then use autofilter on that column and set the filter
condition to non-blanks and it will hide all columns that contain no
data at all.

Alternatively, the following code will loop through each row in the
selection and check its entire row on the worksheet. If there are no
values in any cells on that row, the code sets its hidden property to
true.

Sub Hide()
For i = 1 To Selection.Rows.Count
If Application.CountA(Rows(Selection.Rows(i).Row)) = 0 Then
Selection.Rows(i).Hidden = True
End If
Next i
End Sub

 

[Bob Phillips]

Sub HideRows()
Dim oRow As Range

For Each oRow In Range("A1:A20").EntireRow
oRow.Hidden = Application.CountIf(oRow, "<>") = 0
Next oRow

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)