Unique Case: VBA to Auto Hide Rows Based on Cell Input in Another Sheet

[Walter Murphy]

I scoured the boards and could not find any advice or code that could help me accomplish what I need...I need to auto hide rows based on single cell's input in another sheet. There are 9 different cases and each case requires a different set of non-sequential rows to be hidden. Example:

In Sheet 1, Cell B3 contains a drop down with the 9 different case options (A through I)
In Sheet 2, if "A" is selected from the drop down in Sheet 1, rows 14-19, 20, 27, 29-32, and 58 must be hidden
if "B" is selected from the drop down in Sheet 1, rows 28-32, 49, 53-56, and 67 must be hidden
and so on...


I have existing code in Sheet 1 that hides different worksheets depending on which of the 9 cases is selected in cell B3

Any suggestions? Thank you!!

 

[Micron]

Use a Select Case block as a method of approach? Or are you saying you don't know how to write code that hides rows? Or both? I took it that you're looking for a way to handle the variations.
Several things you could do within that block:
- just hide rows by hard coding them for each of nine cases, or
- your drop down could have a column for those row numbers and pass them to an array in code (that way, you could alter the values without editing code)
That's all that comes to mind at the moment but I'd bet there are other ways to deal with the choices.

Note that wrt getting the values from column 2 of a drop down, I'm assuming it's also possible in Excel, because I know it's possible in Access.

 

[Peter_SSs]

Welcome to the MrExcel board!

Just spelling out in a bit more detail the 'Select Case' structure that could be used. If you already have a Worksheet_Change code in Sheet1 then the code below will need to be incorporated with that. Otherwise, to implement this code ..
1. Right click the Sheet1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. You will need to complete the other 7 scenarios in the space in the Select Case block.
3. Close the Visual Basic window & test. by changing/deleting cell B3

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B3")) Is Nothing Then
    With Sheets("Sheet2")
      .Rows.Hidden = False
      Select Case Range("B3").Value
        Case "A": .Range("14:20, 27:27, 29:32, 58:58").EntireRow.Hidden = True
        Case "B": .Range("28:32, 49:49, 53:56, 67:67").EntireRow.Hidden = True
        
        
      End Select
    End With
  End If
End Sub

 

[Walter Murphy]

Use a Select Case block as a method of approach? Or are you saying you don't know how to write code that hides rows? Or both? I took it that you're looking for a way to handle the variations.
Several things you could do within that block:
- just hide rows by hard coding them for each of nine cases, or
- your drop down could have a column for those row numbers and pass them to an array in code (that way, you could alter the values without editing code)
That's all that comes to mind at the moment but I'd bet there are other ways to deal with the choices.

Note that wrt getting the values from column 2 of a drop down, I'm assuming it's also possible in Excel, because I know it's possible in Access.

Thanks for your response! I don't know how to write code at all. to hide the worksheets, I found a VBA on youtube, copied/pasted and was able to edit to success. But I haven't had any luck with the same approach for hiding rows in other sheets based on the case selected in B3 of Sheet1. I attempted the below posters code by copying/pasting below "End Sub" of the code already in Sheet1. I'm getting this error: "Ambiguous Name detected: Worksheet_Change", the error highlighted is the first line of code: Private Sub Worksheet_Change(ByVal Target As Range).

 

[Walter Murphy]

Welcome to the MrExcel board!

Just spelling out in a bit more detail the 'Select Case' structure that could be used. If you already have a Worksheet_Change code in Sheet1 then the code below will need to be incorporated with that. Otherwise, to implement this code ..
1. Right click the Sheet1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. You will need to complete the other 7 scenarios in the space in the Select Case block.
3. Close the Visual Basic window & test. by changing/deleting cell B3

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B3")) Is Nothing Then
    With Sheets("Sheet2")
      .Rows.Hidden = False
      Select Case Range("B3").Value
        Case "A": .Range("14:20, 27:27, 29:32, 58:58").EntireRow.Hidden = True
        Case "B": .Range("28:32, 49:49, 53:56, 67:67").EntireRow.Hidden = True
       
       
      End Select
    End With
  End If
End Sub

Thank you! I pasted this below "End Sub" of the existing code in Sheet1. I edited the cases and I'm getting the following error..."Ambiguous Name detected: Worksheet_Change", the error highlighted is the first line of code: Private Sub Worksheet_Change(ByVal Target As Range).

 

[Walter Murphy]

Thanks for your response! I don't know how to write code at all. to hide the worksheets, I found a VBA on youtube, copied/pasted and was able to edit to success. But I haven't had any luck with the same approach for hiding rows in other sheets based on the case selected in B3 of Sheet1. I attempted the below posters code by copying/pasting below "End Sub" of the code already in Sheet1. I'm getting this error: "Ambiguous Name detected: Worksheet_Change", the error highlighted is the first line of code: Private Sub Worksheet_Change(ByVal Target As Range).

Sorry, this reply was meant for Micron [If you're confused]

 

[Micron]

For any procedure, you can only have one Sub and one End Sub line and only one procedure by any given name within the same scope. You need to incorporate the suggestion into your procedure, not add it or create a new one with the same name. I suggest post what code you have and it should be fixable. Please use code tags (vba button on posting toolbar) to maintain indentation and readability. I have to go out soon so likely someone else will chip in.

 

[Peter_SSs]

Pretty much repeating what @Micron has said, but here goes anyway ..

I pasted this below "End Sub" of the existing code in Sheet1. I edited the cases and I'm getting the following error..."Ambiguous Name detected: Worksheet_Change",

That is why I said in my previous post ..

If you already have a Worksheet_Change code in Sheet1 then the code below will need to be incorporated with that.

To help combine your existing code and the new code, please post both your original Worksheet_Change code and my Worksheet_Change code but with the modifications that you made for the other 'Case' statements.