Loop through a range of rows and hide based on conditions

  • Thread starter Thread starter troy_lee
  • Start date Start date
T

troy_lee

I want to loop through a range. If any data exists on the row in the
range from Column X over to the last column in use, I want to hide the
row.

I also have questions about null and zero values in the cells. Cells
can have either in the row of data. I am hiding zero values but I am
assigning a zero value via a function for some cells.

Thanks in advance for the help.
 
For those who would like to know, I solved this by modifying code by
Bob Phillips:

If you have Option Explicit turned on, be sure to declare the cell
variable, ie. Dim cell as Range.

For Each cell In Range("X4:X54")
If Application.CountIf(cell.Resize(, 4), ">0") = 0 Then
cell.EntireRow.Hidden = True
End If
Next cell

I will modify the code to be more dynamic, but this concept is sound
and works.
 
Hi,

Regarding the first problem, which I know you have solved - you could add a
column with the formula
=COUNTA(X1:AM1)
Then you could AutoFilter on this new column with the choice 0 checked.

Zero values will be counted, using COUNTA.
 
Hi,

The code you supplied will not count cells that contain negative values,
which does not meet your original request. Also, ">0" will not count zeros
regardless of how they are entered. Mayby you should change to "<>" which
means not blank or "=" which means blank.
 
Back
Top