How to hide all rows with a blank or zero
Category: [Excel] Demo Available
How to hide all rows with a blank or zero
Requirement:
The user found some VBA to hide all rows that do not have any results greater than a zero in them.
For example rows 1 and 3 below should be hidden whilst rows 2 and 4 should still be visible..
| Test | 27-Feb | 15-May | 27-Jul | 15-Aug | 20-Aug | 09-Nov | 14-Dec |
| Count | 0 | 0 | |||||
| Count | 0 | 80 | 0 | ||||
| Count | 0 | 0 | |||||
| Count | 40 |
Unfortunately, not being a coder the user can't work out why it won't work.
The user has attached the .xlsm w/book.
Solution:
Code:
Sub HideRows()
Dim r As Long
For r = 4 To Cells(Rows.Count, "A").End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("D" & r & ":J" & r), ">0") = 0 Then Rows(r).Hidden = True
Next r
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to count blanks if 1 of 2 conditions are met |
| How to use conditional format to highlight the blank cells |
| How to print range excluding blank rows with formulas |
| How to count non blanks 5 years from the last non blank date |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.