If any cells in range E10:E100 contains anything, hide rows of those cells

[jokr1]

If any cells in range E10:E100 contains anything, hide rows of those cells.
If rows already hided, reveal rows.

How is this accomplished with VBA?

 

[Fluff]

Hia
How about

Sub jokr1()
    
    Dim Cl As Range
    
    For Each Cl In Range("E10:E100")
        If Cl.Value <> "" Then Cl.EntireRow.Hidden = Not Cl.EntireRow.Hidden
    Next Cl
    
End Sub

 

[Rick Rothstein]

If any cells in range E10:E100 contains anything, hide rows of those cells.
If rows already hided, reveal rows.

How is this accomplished with VBA?

If you want to allow some rows hidden while others aren't and if you want to toggle those, then use the code Fluff posted in Message #2 . However, if you want all of the rows with data in E10:E100 to be in sync (either they all are hidden or they all are not hidden at the same time), then you can use this non-looping code to do that (Fluff's looping code would also work if you want to stick with that)...

Sub ToggleHiddenStatus()
  On Error GoTo NoFilledCells
  With Range("E10:E100").SpecialCells(xlConstants).EntireRow
    .Hidden = Not .Hidden
  End With
NoFilledCells:
End Sub

 

[jokr1]

I went with Fluff's method, exactly what I needed!

Thanks so much for helping me out so quickly!

 

[Fluff]

Glad to help & thanks for the feedback

 

[jokr1]

Glad to help & thanks for the feedback

One thing I noticed now though:

If I add something to a cell, then run the macro to hide the row. If I then add something to a second cell in that column, that row will be hidden and the first row will be unhidden. Is it possible in this case to hide the second row too without making the first row visible?

 

[Fluff]

Try

Sub jokr1()
    
    Dim Cl As Range
    
    For Each Cl In Range("E10:E100")
        If Cl.Value <> "" Then Cl.EntireRow.Hidden=True
    Next Cl
    
End Sub

 

[Rick Rothstein]

One thing I noticed now though:

If I add something to a cell, then run the macro to hide the row. If I then add something to a second cell in that column, that row will be hidden and the first row will be unhidden.

But that is what you asked for when you said... "If rows already hided, reveal rows."

Is it possible in this case to hide the second row too without making the first row visible?

How would that work along with your desire to unhide hidden rows? I don't see how we could decide between the two competing requests.

 

[jokr1]

But that is what you asked for when you said... "If rows already hided, reveal rows."




How would that work along with your desire to unhide hidden rows? I don't see how we could decide between the two competing requests.

What I mean is:

With the first code, if I add some text to cell A and run the macro, the row with cell A will hide.
If I then add some text to another cell B in same column, then cell B will hide but cell A will be visible again.

So what I would need is:
If cells in column range contain text, then hide all those rows if not already hidden.
If already all the rows with these text cells in column range are hidden, then reveal all these rows.

 

[Fluff]

So what I would need is:
If cells in column range contain text, then hide all those rows if not already hidden.
If already all the rows with these text cells in column range are hidden, then reveal all these rows.

This is what the code supplied by me in post#2 and by Rick in post#3 does.

If I then add some text to another cell B in same column, then cell B will hide but cell A will be visible again.

This is what you initially asked for & then reiterated in the first quote above.


If you want some rows to remain hidden & others to become visible, we will need exact rules as to what to hide & what not to hide.