Macro to Hide/Unhide Rows based on a cell value.

[Michelledryr]

Hi All,

Very new to the Marco's and would really appreciate your assistance to provide me with a macro that can hide/unhide rows base on a value in a certain cell.

I am working on Excel 2013. And have 3 Worksheets which the information on my result sheet is pulling from (2 Sheets with Data + 1 Result sheet)

The value is in Cell B19 in result sheet

Thus if B19 value = FOB rows 49:50 & 58:66 must be hidden
if B19 value = CFR rows 58:66 must be hidden
if B19 value = CIF rows 58:66 must be hidden
Any other value all cells to be visable

Thanking you in advance for your assistance

 

[frank_AL]

Give this code a try.

Sub HideRows()


Dim LR As Long
Dim b19val As String

Sheets("Results").Select  '  Replace "Results" with correct Sheet name if it is not "Results"
LR = Cells(Rows.Count, "B").End(xlUp).Row
b19val = Range("B19").Value
Rows.EntireRow.Hidden = False


If b19val = "FOB" Then
    Rows("49:50").EntireRow.Hidden = True
    Rows("58:66").EntireRow.Hidden = True
ElseIf b19val = "CFR" Or b19val = "CIF" Then
    Rows("58:66").EntireRow.Hidden = True
End If


End Sub

 

[Michelledryr]

@ Frank_AL:

Thanks so much it works with hiding the rows but not unhiding them when I select other values.

Also how to I get the Macro to run automatically ?

You have been a great help thus far

 

[frank_AL]

Michelle,

I have modified to ensure it Unhides all Rows if any other value is entered in B19 and I have also modified to run anytime the cell B19 is changed.

You will need to copy this code to the worksheet this applies to in the VBA Editor.
You do this by double clicking on the Sheet Name and then copying the code in the window that opens.

Let me know if you need more explanation of where to put the code.

Frank

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$B$19" Then
    Dim LR As Long
    Dim b19val As String
    
    LR = Cells(Rows.Count, "B").End(xlUp).Row
    b19val = Range("B19").Value
    Rows.EntireRow.Hidden = False
    
    If b19val = "FOB" Then
        Rows("49:50").EntireRow.Hidden = True
        Rows("58:66").EntireRow.Hidden = True
    ElseIf b19val = "CFR" Or b19val = "CIF" Then
        Rows("58:66").EntireRow.Hidden = True
    Else
        Rows.EntireRow.Hidden = False
    End If
End If


End Sub

 

[Michelledryr]

Hi Frank,

Thank you for the above.

I am either being blond today or need to amend my settings.

When I want to run the code it is giving me a popup where it is requesting a macro Name.

How do I get pass this?

Thanks so much for your help thus far

 

[Rick Rothstein]

I am either being blond today or need to amend my settings.

When I want to run the code it is giving me a popup where it is requesting a macro Name.

How do I get pass this?

Frank did not give you a macro, rather, he gave you event code... you don't run it, it runs automatically in response to an action by the user (in this case, a change to cell values). Here is instruction on how to install event code...

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Michelledryr]

Thanks Guys!

It is working now

BIG THUMBS UP!

 

[Michelledryr]

Frank & Rick,

I will have to be a pain again....

It works beautifully when I type the data into B19.

However when I link B19 to a Dropdown list in another worksheet (Form Cell B13) the event code does not work anymore.

Will you please help me out of my misery one last time .....please?

Thanks for your kind assistance

 

[Rick Rothstein]

Frank & Rick,

I will have to be a pain again....

It works beautifully when I type the data into B19.

However when I link B19 to a Dropdown list in another worksheet (Form Cell B13) the event code does not work anymore.

That is because event code reacts to the change a user makes in a range, not one that Excel makes. If you will still be inputting manually in cell B19 along with the entry made by the drop down, then leave the current code in the worksheet code module alone, otherwise delete it. You will need to install the code (with sheet references added for the sheet where the hiding/unhiding is taking place, as appropriate) in the worksheet code module for the sheet with the drop down on it. This was not my code, but I believe the changes I made to Frank's code below are correct (I took the opportunity to change the variable name to match its new location and to tighten up some of his original code). This is untested but it should work...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$13" Then
    Dim b13val As String
    
    b13val = Target.Value
    
    If b13val = "FOB" Then
        Sheets("Result").Range("49:50,58:66").EntireRow.Hidden = True
    ElseIf b13val = "CFR" Or b13val = "CIF" Then
        Sheets("Result").Rows("58:66").Hidden = True
    Else
        Sheets("Result").Rows.Hidden = False
    End If
End If

End Sub

 

[frank_AL]

Rick,

Thank you for the details on how to install event code! I will keep that for future reference. I expected more detail was needed but I was short on time this morning.
Also, thanks for the modifications to my original code. This is the very reason I use this Form and participate as I learn everyday from experienced developers like yourself!

Frank