Hiding empty rows and columns

  • Thread starter Thread starter Fordy39
  • Start date Start date
F

Fordy39

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

Thanks
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

hide, unhide column or row after protect worksheet 4
Hiding rows or columns based on cell contents 3
Hide Column If Usedrange of Column ISBLANK Follow-up 1
Hide / Unhide each row from StartRow to LastRow for multiple conditions 6
How do you hide multiple row macro 1
Hiding blank rows using a macro 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
auto hide blank rows 1

Back
Top