Hide rows that are formatted a certain way

[CaptainGravyBum]

Happy New Year all!

I've had a few days away from the office and it appears my brain will not function anymore.
I have a simple excel checklist which when a row is complete it will conditionally format as green. I want the users to activate a macro when they are finished using the sheet and it will hide all rows that are green.
Trying to avoid using an autofilter because it can be removed too easily.
I know it's simple, but I'm not getting there fast.
Any help greatly appreciated.

 

[Joe4]

The only way to do that automatically without using Filters is to use VBA.
Are you open to using VBA?

Also, what exactly is your Conditional Formatting Rule that currently turns these rows green?
We will probably want to use the same logic in the VBA code.

 

[CaptainGravyBum]

The only way to do that automatically without using Filters is to use VBA.
Are you open to using VBA?

Also, what exactly is your Conditional Formatting Rule that currently turns these rows green?
We will probably want to use the same logic in the VBA code.

Hi @Joe4 ,
yes, VBA is what I'm after. Basically, if a cell is populated with any text in column L, then the whole row turns green.

 

[JEC]

Probably like this

Sub jec()
On Error Resume Next
Columns("L").SpecialCells(2).EntireRow.Hidden = True
End Sub

 

[Joe4]

Go to the sheet you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor box that shows up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if column L is manually updated and is not empty
    If (Target.Column = 12) And (Target <> "") Then
'       Hide row
        Rows(Target.Row).Hidden = True
    End If
    
End Sub

This should automatically hide the row as column L is manually updated.

 

[CaptainGravyBum]

Go to the sheet you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor box that shows up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   Only run if column L is manually updated and is not empty
    If (Target.Column = 12) And (Target <> "") Then
'       Hide row
        Rows(Target.Row).Hidden = True
    End If
   
End Sub

This should automatically hide the row as column L is manually updated.

Thanks, that works well.
However, is there a way to convert this so it can be run on it's own in a module? Only reason I ask is that I need the user to hide completed at the end of the day rather than it happening as the day goes on. I tried running this manually and it fails with an object error.

 

[Joe4]

Thanks, that works well.
However, is there a way to convert this so it can be run on it's own in a module? Only reason I ask is that I need the user to hide completed at the end of the day rather than it happening as the day goes on. I tried running this manually and it fails with an object error.

Try this version then:

Sub MyHideRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column L with data
    lr = Cells(Rows.Count, "L").End(xlUp).Row

'   Loop thought rows starting with row 2
    For r = 2 To lr
'       See if column L is not empty
        If Cells(r, "L") <> "" Then
'           Hide row
            Rows(r).Hidden = True
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[CaptainGravyBum]

Try this version then:

Sub MyHideRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column L with data
    lr = Cells(Rows.Count, "L").End(xlUp).Row

'   Loop thought rows starting with row 2
    For r = 2 To lr
'       See if column L is not empty
        If Cells(r, "L") <> "" Then
'           Hide row
            Rows(r).Hidden = True
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Perfect!, thank you.

 

[Joe4]

You are welcome.
Glad I was able to help!