Hide Empty and Null Rows
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...
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.
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
