Hiding empty rows and columns

F

Fordy39

Does anyone know the code for hiding all blank rows and columns
in a worksheet.

Thanks
 
F

Frank Kabel

Hi
try the following (adapted from:
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows)

Public Sub HideBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0
Then
Rng.Rows(R).EntireRow.hidden=true
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
Top