Show/Hide rows and columns by drop down selection

[Legacy 48831]

Hello Everyone,

Is it possible to show/hide rows and columns based upon a drop down selection in excel? I have a worksheet I need to create that would allow the user to select a number of rows and a number of columns and then the sheet would either hide or show only that number of rows and columns. If this is possible, could someone please point me in the direction of what to do?

 

[Legacy 48831]

I think I just figured this out, I placed the code below for the rows. Haven't done the columns yet. I would still be interested if anyone knows a better way to do this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D1")) Is Nothing Or Target.Cells.Count > 1 Then
        Exit Sub

    ElseIf Range("D1").Value = "1" Then
        Rows("3:9").EntireRow.Hidden = True
        
    ElseIf Range("D1").Value = "" Then
        Rows("3:9").EntireRow.Hidden = False

    End If

End Sub

 

[My Aswer Is This]

I would do it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/28/2018  5:27:03 PM  EST
    If Intersect(Target, Range("D1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = 1 Then Rows("3:9").Hidden = True
    If Target.Value = "" Then Rows("3:9").Hidden = False
End Sub

 

[Legacy 48831]

How about for the columns? I have been trying to do the code with both the rows and columns and can only get the rows to hide. Not exactly sure. I am using D1 for the rows and F1 for the columns but every time I try to modify the code to have both, I am not seeing any result on the sheet. I must be doing something wrong.

 

[My Aswer Is This]

So when you enter what into F1

What do you want to happen:

If F1 equals 14 do what?

 

[My Aswer Is This]

Since you signed off before answering my questions about columns

Try this modify as needed

Since I have no ideal what columns you want hidden mine hides columns A To C
Modified as needed

It's hard to help when you did not provide specific details about what columns

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/28/2018  7:13:03 PM  EST
'Rows
    If Intersect(Target, Range("D1")) Is Nothing Or Target.Cells.Count > 1 Then
    Else
    If Target.Value = 1 Then Rows("3:9").Hidden = True
    If Target.Value = "" Then Rows("3:9").Hidden = False
    End If
'Columns
    If Intersect(Target, Range("F1")) Is Nothing Or Target.Cells.Count > 1 Then
    Else
    If Target.Value = 3 Then Columns("A:C").Hidden = True
    If Target.Value = "" Then Columns("A:C").Hidden = False
    End If
End Sub

 

[Legacy 48831]

Please accept my apologies for not being online to answer your question. I was having internet issues. As for your question about F1's contents, I am actually working on determining what the cell contents should be and which columns are to be hidden, however your example shows me a number of mistakes I made while doing my testing. I will try your example.

Again, I apologize for the inconvenience due to my internet connection especially since you were so willing to help. I appreciate your assistance.

 

[My Aswer Is This]

Not a problem. Glad I was able to provide something you can work with.
Glad to know you know how to modify script to your needs.

 

[Legacy 48831]

Your sample code worked perfectly! I wanted to let you know that your assistance not only helped me to get the code to work but also shows me where my mistakes were. Thanks again for your help.

 

[My Aswer Is This]

Here is another way to do this.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/28/2018  10:15:01 PM  EST
If Target.Cells.CountLarge > 1 Then Exit Sub
'Rows
    If Target.Address = "$D$1" And Target.Value = 1 Then Rows("3:9").Hidden = True
    If Target.Address = "$D$1" And Target.Value = "" Then Rows("3:9").Hidden = False
    
    'Columns
    If Target.Address = "$F$1" And Target.Value = 3 Then Columns("A:C").Hidden = True
    If Target.Address = "$F$1" And Target.Value = "" Then Columns("A:C").Hidden = False
End Sub