Hi,
I'm wondering if anyone can help me with this VBA problem I'm having.
To lay the backdrop; my understanding of VBA is limited and I've been reading tutorials to implement the functions I want. However, when trying to stack multiple 'If' arguments the code isn't behaving the way I expected it to.
In short, I am using VBA to auto hide columns and or rows depending on the value of a cell which has a pre-determined list using a validation drop down box. The aim of this is to hide a range of columns, or multiple ranges of columns.
So in the attached example, "SuperHero Patrol" when selecting 'Jan' from the drop down in Cell A1, I want the sheet to auto hide all columns where the date isn't January. When selecting 'Feb' it auto hides everything not February and so on. The first time I added the code it seemed to work exactly how I wanted it to; i.e. it would auto hide all columns that weren't January. But when I add the next logic string to auto hide everything that isn't Feb it seems to not work the same way. Instead of hiding the target range, it seems to hide a completely different range. Additionally the first logic argument now seems not to work at all.
So the initial code is as follows;
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 And Target.Value = "Jan" Then
Range("AG:NH").EntireColumn.Hidden = True
Else
Range("AG:NH").EntireColumn.Hidden = False
End If
This achieved what I wanted it to which was to auto hide all columns that weren't January. However, once I expanded it as follows, the code didn't work;
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 And Target.Value = "Jan" Then
Range("AG:NH").EntireColumn.Hidden = True
Else
Range("AG:NH").EntireColumn.Hidden = False
End If
If Target.Column = 1 And Target.Row = 1 And Target.Value = "Feb" Then
Range("B:AF").EntireColumn.Hidden = True
Range("BI:NH").EntireColumn.Hidden = True
Else
Range("B:AF").EntireColumn.Hidden = False
Range("BI:NH").EntireColumn.Hidden = False
End If
If Target.Column = 1 And Target.Row = 1 And Target.Value = "Mar" Then
Range("B:BH").EntireColumn.Hidden = True
Range("CN:NH").EntireColumn.Hidden = True
Else
Range("B:BH").EntireColumn.Hidden = False
Range("CN:NH").EntireColumn.Hidden = False
End If
If Target.Column = 1 And Target.Row = 2 And Target.Value = "Captain America" Then
Range("5:50").EntireRow.Hidden = True
Else
Range("5:50").EntireRow.Hidden = False
End If
If Target.Column = 1 And Target.Row = 2 And Target.Value = "The Hulk" Then
Range("6:50").EntireRow.Hidden = True
Else
Range("6:50").EntireRow.Hidden = False
End If
End Sub
Display More
Same is true to the code to hide the rows.
Can anyone help to explain what is wrong with the code and why it isn't behaving the way I want it to?
Thanks