I found/edited existing VBA code to create a macro that hides empty rows. It worked perfectly, until I had to edit the spreadsheet so that at least one cell in each row has a formula. Most of these formulas do not return anything, but since they do not technically equal 0 my existing macro does not hide the rows. How can I edit the below macro to hid both empty rows AND those that only have null values? Thanks!
Sub HideEmpties()
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
For n = nFirstRow To nLastRow
If Application.WorksheetFunction.CountA(Rows(n)) = 0 Then
Rows(n).EntireRow.Hidden = True
End If
Next
End Sub
2 Answers
A slight variation on your code:
Sub HideEmpties()
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
For n = nFirstRow To nLastRow
If Application.WorksheetFunction.CountBlank(Rows(n)) = Columns.Count Then
Rows(n).EntireRow.Hidden = True
End If
Next
End Sub
Because COUNTBLANK() treats nulls like blanks.
You might try changing 0 to 1.
