Hide/Unhide row based on cell value

Hi, I am very new to writing VBA so for many of you this will most likely be a very simple question. I want to hide/unhide rows based on whether or not a cell have a value in it.
If L18 in blank then Row 171 is hided; if L19 is blank then Row 172 is hidden and so on to L168. I have it working for one cell/Row but how do I set it to do this for the range?
Here is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False

Select Case Range("L18").Value = ""

Case True: Rows("171").Hidden = True

Case False: Rows("171").Hidden = False

End Select

End Sub

Welcome to the Forum TrenchRT!

I would do it with a loop. Also, Select Case is overkill here. And if you turn off screen updating, remember to turn it back on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
   Dim R As Long ' row
   
   Application.ScreenUpdating = False
   
   For R = 18 To 168
      Rows(R + 153).Hidden = (Cells(R, "L") = "")
   Next R

   Application.ScreenUpdating = True


End Sub

this solution will work as well:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wb     As Workbook
    Dim ws     As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet3")
    Dim keycells As Range
    Set keycells = ws.Range("L18:L168")
    Dim z      As Long
    
        With ws
        If Not Intersect(keycells, Range(Target.Address)) Then
            If Target.Value = "" Then
                'hide row
                z = 153 + Split(Target.Address, "$")(2)
                Rows(z).Hidden = True
            Else
                'unhide row
                z = 153 + Split(Target.Address, "$")(2)
                Rows(z).Hidden = False
            End If
        End If
    End With
End Sub

more code that what 6stringjazzer has but maybe this will work for you better.

so copy this and put it in the sheet code (sheet1, sheet2, etc)

Originally Posted by TrenchRT

when I run the solution from dmcgov I get

Run-time error "13"
Type mismatch

and debugger highlights on this line

If Not Intersect(keycells, Range(Target.Address)) Then

It should be

If Not Intersect(keycells, Target) Then

Originally Posted by TrenchRT

Thanks again guys, this is working great!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.