VBA - Hide / Unhide rows based on cell value

[rhilligus]

Hello,

Thank you to all who help out on the forum... You make me much smarter than i am.

I am working on a sheet and am stuck with some VBA coding.

As the title says i am looking to hide and unhide rows based on cell values. I am currently using a "extractwordinstring" formula to provide a true false statement. In my case the true / false shows in L23 : L33. I am looking for a formula that if L23 is true unhides row 34:35, if L24 unhide row 36:37, ect...

I have been able to get as far as the first set of rows hiding and unhiding using:

Private Sub worksheet_Change(ByVal Target As Range)
If Range("l24").Value = False Then
Rows("34:35").EntireRow.Hidden = True
ElseIf Range("L24").Value = True Then
Rows("34:35").EntireRow.Hidden = False


End If
End Sub

How can i expand upon this?

Thanks again,
Rob

 

[RickXL]

Hi,

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long
    Dim r As Range
    If Not Intersect(Target, Range("L23:L33")) Is Nothing Then
        For Each r In Range("L23:L33")
            iRow = 2 * (r.Row - 23) + 34
            Rows(iRow & ":" & iRow + 1).EntireRow.Hidden = Not r.Value
        Next
    End If
End Sub

How does it work?

The

If Not Intersect(Target, Range("L23:L33")) Is Nothing Then

means if the target range, that is the changed cells, overlap with L23:L33 then do the next bit. This is just a way of ignoring changes that are made elsewhere in the sheet. It would work without that but the rows would be processed every time the sheet changed.

The "For Each" loop looks at each cell in L23:L33 to make sure its value is used to hide or show the relevant rows.

The iRow calculation is a way of converting the row numbers with the TRUE or FALSE settings into the row numbers to be hidden or shown.

If the value is TRUE then it needs to be not hidden so NOT the value is used in the formula.

 

[rhilligus]

Hello,

Thanks for the response. Unfortunately, this code does not seem to be working.

I placed it on the worksheet VBA page and if i go through the list to change the cells to true/false the rows do not hide or show.

I have also tried this in a module and had the same result. Also, my apologies the beginning cell with true/false is L24 and continues down to L33.

Could this be causing the issue?

Thanks,
Rob

 

[RickXL]

The code needs to go in the space for worksheet code. In the worksheet that will have the data.

The code to work on the L24:L33 range is:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long
    Dim r As Range
    If Not Intersect(Target, Range("L24:L33")) Is Nothing Then
        For Each r In Range("L24:L33")
            iRow = 2 * (r.Row - 24) + 34
            Rows(iRow & ":" & iRow + 1).EntireRow.Hidden = Not r.Value
        Next
    End If
End Sub

I tested it by putting TRUE into cells L24:L33 and by filling cells L34:L54 with data. L54 does not get hidden as it is just outside the range of affected cells but L34:L53 come and go as I change the TRUE and FALSE values.

You may have accidently turned off Events. So try the program on a freshly opened wokbook with no other macros in it. A common way to turn off events by accident is to have a macro that starts and ends like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False


    Application.EnableEvents = True
End Sub

That code is OK but if it errors in the middle then the final line is not done - leaving the events disabled.