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

 

[Jenny_MO]

Hi guys,

I understand this is an old question, but I've had the issue creep up on a current project, and this is is my first attempt to use VBA.

In my workbook, a user will enter a value 1-8 in cell C18 - I need to hide/unhide rows based on the value entered in that cell, with the value 8 having no hidden cells. When using the code below, a user can enter a value 7 and it will show the proper rows, then go down in value, but when going up from lets say a 1 value to a 3 value, the worksheet will not show the proper number of rows. Any thoughts on how to fix that?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$18" Then
    Dim C18val As String
    
    C18val = Target.Value
    
    If C18val = "" Then
        Rows("20:52").EntireRow.Hidden = True
    ElseIf C18val = "1" Then
        Rows("24:52").EntireRow.Hidden = True
    ElseIf C18val = "2" Then
        Rows("28:52").EntireRow.Hidden = True
    ElseIf C18val = "3" Then
        Rows("32:52").EntireRow.Hidden = True
    ElseIf C18val = "4" Then
        Rows("36:52").EntireRow.Hidden = True
    ElseIf C18val = "5" Then
        Rows("40:52").EntireRow.Hidden = True
    ElseIf C18val = "6" Then
        Rows("44:52").EntireRow.Hidden = True
    ElseIf C18val = "7" Then
        Rows("48:52").EntireRow.Hidden = True
    ElseIf C18val = "8" Then
        Rows("52:52").EntireRow.Hidden = False
    End If
End If
End Sub

Thanks!
Jenny

 

[Fluff]

Hi & welcome to the board.
Try adding this line as shown

If Target.Address = "$C$18" Then
    Dim C18val As String
   [COLOR=#ff0000] Rows("20:52").Hidden = False[/COLOR]
    C18val = Target.Value

 

[Jenny_MO]

Thanks Fluff! That makes it work perfectly.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Giraud]

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

Hey, sorry I have no schooling in excel, I do it for fun on the side to help out with some of my work excel projects.

I am working on a similar project. The original code on this forum worked great(only manual entry in the cell), but i need it to work with coding in the cell, example =Cell#.

When tried the new code, it get run time errors. I updated the code for my project. Could some one help me with this. thanks.


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$A$2" Then
Dim A2val As String

A2val = Target.Value

If A2val = "1" Then
Sheets("Result").Range("3:12").EntireRow.Hidden = True (Run time error here)
ElseIf A2val = "2" Then
Sheets("Result").Rows("65:75").Hidden = True (Run time error here)
Else
Sheets("Result").Rows.Hidden = False (Run time error here)
End If

End Sub