thebazman
07-19-2008, 02:00 AM
hi all,
im hoping someone will be able to help with this as i dont have the knowledge to write it myself, and it would make a huge difference.
ive been making an instalment spreadsheet for our team at work,
(attached)
it contains 2 sheets a data and a print sheet, the print sheet copies certain cells from the data sheet formatted and with less info for when we need hard copies.
i need to 3 things, on the data sheet i need to hide entire columns based on the criteria of cell C9 (will have a value of 0 - 12)
im currently using this code, it works but im sure its probably an inefficient way
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("C9") Then
Select Case UCase(Target.Value)
Case "0"
Range("H:AQ").EntireColumn.Hidden = True
Case "1"
Range("H:J").EntireColumn.Hidden = False
Range("K:AQ").EntireColumn.Hidden = True
Case "2"
Range("H:M").EntireColumn.Hidden = False
Range("N:AQ").EntireColumn.Hidden = True
Case "3"
Range("H:P").EntireColumn.Hidden = False
Range("Q:AQ").EntireColumn.Hidden = True
Case "4"
Range("H:S").EntireColumn.Hidden = False
Range("T:AQ").EntireColumn.Hidden = True
Case "5"
Range("H:V").EntireColumn.Hidden = False
Range("W:AQ").EntireColumn.Hidden = True
Case "6"
Range("H:Y").EntireColumn.Hidden = False
Range("Z:AQ").EntireColumn.Hidden = True
Case "7"
Range("H:AB").EntireColumn.Hidden = False
Range("AC:AQ").EntireColumn.Hidden = True
Case "8"
Range("H:AE").EntireColumn.Hidden = False
Range("AF:AQ").EntireColumn.Hidden = True
Case "9"
Range("H:AH").EntireColumn.Hidden = False
Range("AI:AQ").EntireColumn.Hidden = True
Case "10"
Range("H:AK").EntireColumn.Hidden = False
Range("AL:AQ").EntireColumn.Hidden = True
Case "11"
Range("H:AN").EntireColumn.Hidden = False
Range("AO:AQ").EntireColumn.Hidden = True
Case "12"
Range("H:AQ").EntireColumn.Hidden = False
End Select
End If
End Sub
the 2nd thing is on the print page i need to do the same thing as above but as all the cells contain formulas such as =IF(COUNTA(Data!C9),Data!C9,"") it doesnt work, the value in the cell changes but wont hide columns unless its actually typed in again,
i done that instead of just =Data!C9 so that it doesnt show a 0 when there is nothing in the referenced cell
the final thing is also on the print sheet i want to hide entire rows based on the value of a cell, eg if G19 is greater than zero unhide Row 19, if anything else, hide Row 19.
this will repeat untill row 56, also the contents of say G19 is, as above, just a reference to a cell on the Data sheet, dont know if that makes any difference.
sorry its long, as thank you so much for any help you might have.
im hoping someone will be able to help with this as i dont have the knowledge to write it myself, and it would make a huge difference.
ive been making an instalment spreadsheet for our team at work,
(attached)
it contains 2 sheets a data and a print sheet, the print sheet copies certain cells from the data sheet formatted and with less info for when we need hard copies.
i need to 3 things, on the data sheet i need to hide entire columns based on the criteria of cell C9 (will have a value of 0 - 12)
im currently using this code, it works but im sure its probably an inefficient way
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("C9") Then
Select Case UCase(Target.Value)
Case "0"
Range("H:AQ").EntireColumn.Hidden = True
Case "1"
Range("H:J").EntireColumn.Hidden = False
Range("K:AQ").EntireColumn.Hidden = True
Case "2"
Range("H:M").EntireColumn.Hidden = False
Range("N:AQ").EntireColumn.Hidden = True
Case "3"
Range("H:P").EntireColumn.Hidden = False
Range("Q:AQ").EntireColumn.Hidden = True
Case "4"
Range("H:S").EntireColumn.Hidden = False
Range("T:AQ").EntireColumn.Hidden = True
Case "5"
Range("H:V").EntireColumn.Hidden = False
Range("W:AQ").EntireColumn.Hidden = True
Case "6"
Range("H:Y").EntireColumn.Hidden = False
Range("Z:AQ").EntireColumn.Hidden = True
Case "7"
Range("H:AB").EntireColumn.Hidden = False
Range("AC:AQ").EntireColumn.Hidden = True
Case "8"
Range("H:AE").EntireColumn.Hidden = False
Range("AF:AQ").EntireColumn.Hidden = True
Case "9"
Range("H:AH").EntireColumn.Hidden = False
Range("AI:AQ").EntireColumn.Hidden = True
Case "10"
Range("H:AK").EntireColumn.Hidden = False
Range("AL:AQ").EntireColumn.Hidden = True
Case "11"
Range("H:AN").EntireColumn.Hidden = False
Range("AO:AQ").EntireColumn.Hidden = True
Case "12"
Range("H:AQ").EntireColumn.Hidden = False
End Select
End If
End Sub
the 2nd thing is on the print page i need to do the same thing as above but as all the cells contain formulas such as =IF(COUNTA(Data!C9),Data!C9,"") it doesnt work, the value in the cell changes but wont hide columns unless its actually typed in again,
i done that instead of just =Data!C9 so that it doesnt show a 0 when there is nothing in the referenced cell
the final thing is also on the print sheet i want to hide entire rows based on the value of a cell, eg if G19 is greater than zero unhide Row 19, if anything else, hide Row 19.
this will repeat untill row 56, also the contents of say G19 is, as above, just a reference to a cell on the Data sheet, dont know if that makes any difference.
sorry its long, as thank you so much for any help you might have.