Solved: Show/Hide Columns and rows depending on cell value

[OhGorgeous1]

I have the following code (see below) and feel there must be a way of writing this in a better more slimlined manner but have no idea where to start, is this possible?? if so HELP please.

Thanks in advance



If Range("D8").Value = 1 Then
Columns("F:L").EntireColumn.Hidden = False
Else
Columns("F:L").EntireColumn.Hidden = True
End If
If Range("D8").Value = 2 Then
Columns("M:S").EntireColumn.Hidden = False
Else
Columns("M:S").EntireColumn.Hidden = True
End If
If Range("D8").Value = 3 Then
Columns("T:Z").EntireColumn.Hidden = False
Else
Columns("T:Z").EntireColumn.Hidden = True
End If
If Range("D8").Value = 4 Then
Columns("AA:AG").EntireColumn.Hidden = False
Else
Columns("AA:AG").EntireColumn.Hidden = True
End If
If Range("D8").Value = 5 Then
Columns("AH:AN").EntireColumn.Hidden = False
Else
Columns("AH:AN").EntireColumn.Hidden = True
End If
If Range("D8").Value = 6 Then
Columns("AO:AU").EntireColumn.Hidden = False
Else
Columns("AO:AU").EntireColumn.Hidden = True
End If
If Range("D8").Value = 7 Then
Columns("AV:BB").EntireColumn.Hidden = False
Else
Columns("AV:BB").EntireColumn.Hidden = True
End If
If Range("D8").Value = "8a" Then
Columns("BC:BI").EntireColumn.Hidden = False
Else
Columns("BC:BI").EntireColumn.Hidden = True
End If
If Range("D8").Value = "8b" Then
Columns("BJ:BP").EntireColumn.Hidden = False
Else
Columns("BJ:BP").EntireColumn.Hidden = True
End If
If Range("D8").Value = "8c" Then
Columns("BQ:BW").EntireColumn.Hidden = False
Else
Columns("BQ:BW").EntireColumn.Hidden = True
End If
If Range("D8").Value = "8d" Then
Columns("BX:CD").EntireColumn.Hidden = False
Else
Columns("BX:CD").EntireColumn.Hidden = True
End If
If Range("D8").Value = 9 Then
Columns("CE:CK").EntireColumn.Hidden = False
Else
Columns("CE:CK").EntireColumn.Hidden = True
End If

[Bob Phillips]

Columns("F:L").Hidden = Not (Range("D8").Value = 1)
Columns("M:S").Hidden = Not (Range("D8").Value = 2)
Columns("T:Z").Hidden = Not (Range("D8").Value = 3)
Columns("AA:AG").Hidden = Not (Range("D8").Value = 4)
Columns("AH:AN").Hidden = Not (Range("D8").Value = 5)
Columns("AO:AU").Hidden = Not (Range("D8").Value = 6)
Columns("AV:BB").Hidden = Not (Range("D8").Value = 7)
Columns("BC:BI").Hidden = Not (Range("D8").Value = "8a")
Columns("BJ:BP").Hidden = Not (Range("D8").Value = "8b")
Columns("BQ:BW").Hidden = Not (Range("D8").Value = "8c")
Columns("BX:CD").Hidden = Not (Range("D8").Value = "8d")
Columns("CE:CK").Hidden = Not (Range("D8").Value = 9)

[OhGorgeous1]

Many thanks, I will give it a go and let you know if I have any problems (I for see no!)

[OhGorgeous1]

Thanks again xld it works great. I am guessing the same principle applies if hiding rows

Rows("B14:B104").Hidden = Not (Range("B9").Value = 1)

[Bob Phillips]

Thanks again xld it works great. I am guessing the same principle applies if hiding rows

Rows("B14:B104").Hidden = Not (Range("B9").Value = 1)

Same principle, but you don't add the column letters else you will have to qualify with entirerow.



Rows("14:104").Hidden = Not (Range("B9").Value = 1)

'o

Range("B14:B104").Entirerow.Hidden = Not (Range("B9").Value = 1)


I prefer the former, I have a real downer on Entirerow, it is rarely needed.

[OhGorgeous1]

Thanks again, works great