Hide/Unhide rows in range if cell value = criteria

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Happy (almost) Friday!


    The way this sheet is set up, each cell in Range B7:B75 contains a formula that updates to display a "0, 1, or 2" depending on information entered elsewhere on the sheet. I'm trying to write the macro so that whenever a cell in range B7:B75 =0, that particular row will hide. I also need it to unhide when it no longer = 0. I need this to update in real time whenever those values change.


    I already have a macro running on this sheet to hide columns in a similar manner (which works perfectly), but I can't figure out the hide/unhide rows part. Not sure if I should try to add it to my existing macro, or if I should create a second one to handle the rows... Here's the macro that works for the columns:


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B1").Value = 4 Then
        Columns("H:N").EntireColumn.Hidden = True
    Else
        Columns("H:N").EntireColumn.Hidden = False
    End If
End Sub


    Any help on this would be greatly appreciated!


    Thanks,
    DT

    Re: Hide/Unhide rows in range if cell value = criteria


    You can try this.


    I can't guarantee there won't be issues since I can't see your formulas and I don't have your workbook to test with.


    This code must be installed in the sheet module for the sheet in question.


    Code
    Private Sub Worksheet_Calculate()
Dim r As Range
    For Each r In Range("B7:B75")
        If r.Value = 0 Then
            r.EntireRow.Hidden = True
        Else
            r.EntireRow.Hidden = False
        End If
    Next r
End Sub

    Bruce :cool:

    Re: Hide/Unhide rows in range if cell value = criteria


    Thanks for the reply! Not sure what's going on, but the code didn't do anything when I changed things on the sheet, but when I ran it manually - the whole sheet locked up and I had to restart excel.

    Re: Hide/Unhide rows in range if cell value = criteria


    You can't run it manually it's event code.


    If by running it manually you mean you put the cursor in the code and hit F5 or link a button or go to the macros menu, then that means you didn't install it in the worksheet module and also explains why it "didn't do anything", it's not installed in the worksheet module.


    See my previous post.

    Bruce :cool:

    Re: Hide/Unhide rows in range if cell value = criteria


    Please excuse my ignorance- yes I put the cursor in the code and hit F5.


    If by installing it in the worksheet module you mean right clicking the worksheet and selecting view code, and pasting the code under the code I already had there, then that's what I did.


    Perhaps they don't like running together? Not familiar with rules surrounding event codes, or if there's an order they need to be placed in.


    When I took the previous (hide columns) code out, and put in your code for the rows by itself, I got a run-time error '2147417848 (80010108)': Method 'Hidden' of object 'Range' failed.

    Re: Hide/Unhide rows in range if cell value = criteria


    Okay my mistake, apologies, you can run the calculate event code manually, maybe because it doesn't have any parameters that it's looking for.


    So if installed in the worksheet module, then it should run when the sheet calculates.


    My concern about not having your sheet to test on and seeing your formulas is because I don't know what problems can arise.


    I made a small test sheet with some formulas and it worked fine, you seemed to be getting into an infinite loop and I can't say why without seeing the actual workbook.


    Your worksheet is unlocked, the rows in question are not locked?

    Bruce :cool:

    Re: Hide/Unhide rows in range if cell value = criteria


    The whole worksheet is unlocked at the moment, but I intend to lock it once I'm done.


    The values in the range B7:B75 are populated by a reference to another sheet. For example, B7 of my range says =Reference!A3. On the Reference sheet, cell A3 is determined by an index match function that is pulling from that reference sheet.

    Re: Hide/Unhide rows in range if cell value = criteria


    It would really be helpful if you uploaded the workbook, you only need the two sheets and you can use dummy data.


    Use the same type of data in the dummy data, in other words if it's a name just make one up but don't change the data to something other than the type it was and don't just delete the data and not replace it with something similar.


    You don't need data in all 75 rows if all the data is the same type just put in a few and we should be good.


    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

    Bruce :cool:

    Re: Hide/Unhide rows in range if cell value = criteria


    Try this. :cool:


    Bruce :cool:

    Re: Hide/Unhide rows in range if cell value = criteria


    That worked like a champ, thanks! Is there a way protect the sheet without interfering with the code - or do I just need to keep it unlocked?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login