Hide rows based on Column L

[ExcelRoy]

Hi,

Is there a way to hide the rows with anything other than "live" in column L

Many thanks

 

[JLGWhiz]

You can use a macro to hide them.

Sub t()
If Sheet1.Range("A1") <> "" Then
    Rows("2:5").Hidden = True
Else
    Rows("2:5").Hidden = False
End If
End Sub

You can attach the above macro to a Forms Control button and when the button is clicked it will look at cell A1 of Sheet1. If cell A1 has data entered, rows 2 thru 5 will be hidden, If A1 is blank, then Rows 2 thru 5 will be visible.

 

[ExcelRoy]

Hi JLGWhiz,

Many thanks, nice work

but this hides everything which I thought was what I wanted

Maybe just hide "Live", "Run", "Ord","Sto" if its in column L

is this possible

Many thanks

 

[JLGWhiz]

If the values are stand alone values.

Sub t2()
Dim c As Range
With ActiveSheet
    For Each c In .Range("L2", .Cells(Rows.Count, "L").End(xlUp))
        If LCase(c.Value) = "live" Or LCase(c.Value) = "run" Or LCase(c.Value) = "ord" Or LCase(c.Value) = "sto" Then
            c.EntireRow.Hidden = True
        End If
    Next
End With
End Sub

If the values are embedded in other text

Sub t3()
Dim c As Range
With ActiveSheet
    For Each c In .Range("L2", .Cells(Rows.Count, "L").End(xlUp))
        If InStr(LCase(c.Value), "live") > 0 Or InStr(LCase(c.Value), "ord") Or _
        InStr(LCase(c.Value), "run") > 0 Or InStr(LCase(c.Value), "sto") > 0 Then
            c.EntireRow.Hidden = True
        End If
    Next
End With
End Sub

If the strings are embedded in other text then results can be other than desired because VBA will look for any occurrence of that sequence, not just the beginning of a character group.

 

[ExcelRoy]

Hi JLGWhiz,

looks great but doesn't seem to work?

Thanks

 

[JLGWhiz]

Just re-tested both and the both give expected results. Code should be run from the standard module1. Note that one code is for embedded text using the InStr method while the other is for cells which have only the values as listed in the OP. Actually, the InStr should work on either type. It would help if you could elaborate on the 'doesn't seem to work' comment by saying it errors out if it does error, and give the error message and line highlighted when debug button is clicked. Or state that it completes without hiding the rows. Or any other comments that describe what it does or does not do. Vague comments are difficult to analyze.

 

[ExcelRoy]

Hi JLGWhiz,

sorry for the late reply
|
When I run the macro nothing happens at all, its as if I never run anything in the first place

I do have other codes working maybe a clash?

Thanks