I've been following these forums for quite some time and finally have something to ask all the experts here.
I am working on learning to code in VBA, and I've been using code that I've found around the web to build up on what I need for my worksheet.
My objective is to hide rows and columns on different sheets based on the cell value of the first tab in my worksheet. The issue I'm experiencing is that with the code I'm using, Excel is VERY slow for only this workbook. As this is a template I'm building up for my team to use, I'd like to at least fake it that I know what I'm doing and not slow every down by as much as I'd help speed up some things.
Sheet1:
Sheet2:
Sheet6:
Sheet9:
You can see in Sheet9 that I tried to add some code that would, hopefully, speed things up (though it seems to have done nothing).
I'd appreciate any help or tips. If anything in my post is formatted incorrectly, I'll fix whatever anyone points out.
Thank you to everyone just for looking at this!
I am working on learning to code in VBA, and I've been using code that I've found around the web to build up on what I need for my worksheet.
My objective is to hide rows and columns on different sheets based on the cell value of the first tab in my worksheet. The issue I'm experiencing is that with the code I'm using, Excel is VERY slow for only this workbook. As this is a template I'm building up for my team to use, I'd like to at least fake it that I know what I'm doing and not slow every down by as much as I'd help speed up some things.
Sheet1:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$K$1" Then
Select Case Target.Value
Case "1":
Rows("9:17").Hidden = True
Rows("22:30").Hidden = True
Rows("34:42").Hidden = True
Rows("46:54").Hidden = True
Rows("58:66").Hidden = True
Rows("70:78").Hidden = True
Rows("83:91").Hidden = True
Rows("96:104").Hidden = True
Rows("109:117").Hidden = True
Rows("122:130").Hidden = True
Rows("136:144").Hidden = True
Rows("148:156").Hidden = True
Rows("160:168").Hidden = True
Rows("173:181").Hidden = True
Rows("186:194").Hidden = True
Rows("199:207").Hidden = True
Rows("212:220").Hidden = True
Rows("225:233").Hidden = True
Rows("239:247").Hidden = True
Rows("253:261").Hidden = True
Case "2":
Rows("10:17").Hidden = True
Rows("23:30").Hidden = True
Rows("35:42").Hidden = True
Rows("47:54").Hidden = True
Rows("59:66").Hidden = True
Rows("71:78").Hidden = True
Rows("84:91").Hidden = True
Rows("97:104").Hidden = True
Rows("110:117").Hidden = True
Rows("123:130").Hidden = True
Rows("137:144").Hidden = True
Rows("149:156").Hidden = True
Rows("161:168").Hidden = True
Rows("174:181").Hidden = True
Rows("187:194").Hidden = True
Rows("200:207").Hidden = True
Rows("213:220").Hidden = True
Rows("226:233").Hidden = True
Rows("240:247").Hidden = True
Rows("254:261").Hidden = True
Case "3":
Rows("11:17").Hidden = True
Rows("24:30").Hidden = True
Rows("36:42").Hidden = True
Rows("48:54").Hidden = True
Rows("60:66").Hidden = True
Rows("72:78").Hidden = True
Rows("85:91").Hidden = True
Rows("98:104").Hidden = True
Rows("111:117").Hidden = True
Rows("124:130").Hidden = True
Rows("138:144").Hidden = True
Rows("150:156").Hidden = True
Rows("162:168").Hidden = True
Rows("175:181").Hidden = True
Rows("188:194").Hidden = True
Rows("201:207").Hidden = True
Rows("214:220").Hidden = True
Rows("227:233").Hidden = True
Rows("241:247").Hidden = True
Rows("255:261").Hidden = True
Case "4":
Rows("12:17").Hidden = True
Rows("25:30").Hidden = True
Rows("37:42").Hidden = True
Rows("49:54").Hidden = True
Rows("61:66").Hidden = True
Rows("73:78").Hidden = True
Rows("86:91").Hidden = True
Rows("99:104").Hidden = True
Rows("112:117").Hidden = True
Rows("125:130").Hidden = True
Rows("139:144").Hidden = True
Rows("151:156").Hidden = True
Rows("163:168").Hidden = True
Rows("176:181").Hidden = True
Rows("189:194").Hidden = True
Rows("202:207").Hidden = True
Rows("215:220").Hidden = True
Rows("228:233").Hidden = True
Rows("242:247").Hidden = True
Rows("256:261").Hidden = True
Case "5":
Rows("13:17").Hidden = True
Rows("26:30").Hidden = True
Rows("38:42").Hidden = True
Rows("50:54").Hidden = True
Rows("62:66").Hidden = True
Rows("74:78").Hidden = True
Rows("87:91").Hidden = True
Rows("100:104").Hidden = True
Rows("113:117").Hidden = True
Rows("126:130").Hidden = True
Rows("140:144").Hidden = True
Rows("152:156").Hidden = True
Rows("164:168").Hidden = True
Rows("177:181").Hidden = True
Rows("190:194").Hidden = True
Rows("203:207").Hidden = True
Rows("216:220").Hidden = True
Rows("229:233").Hidden = True
Rows("243:247").Hidden = True
Rows("257:261").Hidden = True
Case "6":
Rows("14:17").Hidden = True
Rows("27:30").Hidden = True
Rows("39:42").Hidden = True
Rows("51:54").Hidden = True
Rows("63:66").Hidden = True
Rows("75:78").Hidden = True
Rows("88:91").Hidden = True
Rows("101:104").Hidden = True
Rows("114:117").Hidden = True
Rows("127:130").Hidden = True
Rows("141:144").Hidden = True
Rows("153:156").Hidden = True
Rows("165:168").Hidden = True
Rows("178:181").Hidden = True
Rows("191:194").Hidden = True
Rows("204:207").Hidden = True
Rows("217:220").Hidden = True
Rows("230:233").Hidden = True
Rows("244:247").Hidden = True
Rows("258:261").Hidden = True
Case "7":
Rows("15:17").Hidden = True
Rows("28:30").Hidden = True
Rows("40:42").Hidden = True
Rows("52:54").Hidden = True
Rows("64:66").Hidden = True
Rows("76:78").Hidden = True
Rows("89:91").Hidden = True
Rows("102:104").Hidden = True
Rows("115:117").Hidden = True
Rows("128:130").Hidden = True
Rows("142:144").Hidden = True
Rows("154:156").Hidden = True
Rows("166:168").Hidden = True
Rows("179:181").Hidden = True
Rows("192:194").Hidden = True
Rows("205:207").Hidden = True
Rows("218:220").Hidden = True
Rows("231:233").Hidden = True
Rows("245:247").Hidden = True
Rows("259:261").Hidden = True
Case "8":
Rows("16:17").Hidden = True
Rows("29:30").Hidden = True
Rows("41:42").Hidden = True
Rows("53:54").Hidden = True
Rows("65:66").Hidden = True
Rows("77:78").Hidden = True
Rows("90:91").Hidden = True
Rows("103:104").Hidden = True
Rows("116:117").Hidden = True
Rows("129:130").Hidden = True
Rows("143:144").Hidden = True
Rows("155:156").Hidden = True
Rows("167:168").Hidden = True
Rows("180:181").Hidden = True
Rows("193:194").Hidden = True
Rows("206:207").Hidden = True
Rows("219:220").Hidden = True
Rows("232:233").Hidden = True
Rows("246:247").Hidden = True
Rows("260:261").Hidden = True
Case "9":
Rows("17").Hidden = True
Rows("30").Hidden = True
Rows("42").Hidden = True
Rows("54").Hidden = True
Rows("66").Hidden = True
Rows("78").Hidden = True
Rows("91").Hidden = True
Rows("104").Hidden = True
Rows("117").Hidden = True
Rows("130").Hidden = True
Rows("144").Hidden = True
Rows("156").Hidden = True
Rows("168").Hidden = True
Rows("181").Hidden = True
Rows("194").Hidden = True
Rows("207").Hidden = True
Rows("220").Hidden = True
Rows("233").Hidden = True
Rows("247").Hidden = True
Rows("261").Hidden = True
Case "10":
Rows("9:300").Hidden = False
Case "":
Rows("9:300").Hidden = False
End Select
End If
Application.EnableEvents = True
End Sub
Sheet2:
Code:
Private Sub worksheet_calculate()
Static OldVal As Variant
If Range("$A$2").Value <> OldVal Then
OldVal = Range("$A$2").Value
Call ColHider
End If
End Sub
Sub ColHider()
Application.EnableEvents = False
If Range("$A$2").Value = "1" Then
Range("N:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "2" Then
Range("AC:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "3" Then
Range("AR:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "4" Then
Range("BG:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "5" Then
Range("BV:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "6" Then
Range("CK:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "7" Then
Range("CZ:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "8" Then
Range("DO:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "9" Then
Range("ED:ER").EntireColumn.Hidden = True
ElseIf Range("$A$2").Value = "10" Then
Range("N:ER").EntireColumn.Hidden = False
ElseIf Range("$A$2").Value = "0" Then
Range("N:ER").EntireColumn.Hidden = False
End If
Application.EnableEvents = True
End Sub
Code:
Private Sub worksheet_calculate()
Static OldVal2 As Variant
If Range("$F$1").Value <> OldVal2 Then
OldVal2 = Range("$F$1").Value
Call RowHider2
End If
End Sub
Sub RowHider2()
Application.EnableEvents = False
If Range("$F$1").Value = "1" Then
Rows("9:17").Hidden = True
Rows("39:47").Hidden = True
Rows("51:59").Hidden = True
Rows("63:71").Hidden = True
Rows("75:83").Hidden = True
Rows("87:95").Hidden = True
ElseIf Range("$F$1").Value = "2" Then
Rows("10:17").Hidden = True
Rows("40:47").Hidden = True
Rows("52:59").Hidden = True
Rows("64:71").Hidden = True
Rows("76:83").Hidden = True
Rows("88:95").Hidden = True
ElseIf Range("$F$1").Value = "3" Then
Rows("11:17").Hidden = True
Rows("41:47").Hidden = True
Rows("53:59").Hidden = True
Rows("65:71").Hidden = True
Rows("75:83").Hidden = True
Rows("87:95").Hidden = True
ElseIf Range("$F$1").Value = "4" Then
Rows("12:17").Hidden = True
Rows("42:47").Hidden = True
Rows("54:59").Hidden = True
Rows("66:71").Hidden = True
Rows("78:83").Hidden = True
Rows("90:95").Hidden = True
ElseIf Range("$F$1").Value = "5" Then
Rows("13:17").Hidden = True
Rows("43:47").Hidden = True
Rows("55:59").Hidden = True
Rows("67:71").Hidden = True
Rows("79:83").Hidden = True
Rows("91:95").Hidden = True
ElseIf Range("$F$1").Value = "6" Then
Rows("14:17").Hidden = True
Rows("44:47").Hidden = True
Rows("56:59").Hidden = True
Rows("68:71").Hidden = True
Rows("80:83").Hidden = True
Rows("92:95").Hidden = True
ElseIf Range("$F$1").Value = "7" Then
Rows("15:17").Hidden = True
Rows("45:47").Hidden = True
Rows("57:59").Hidden = True
Rows("69:71").Hidden = True
Rows("81:83").Hidden = True
Rows("93:95").Hidden = True
ElseIf Range("$F$1").Value = "8" Then
Rows("16:17").Hidden = True
Rows("46:47").Hidden = True
Rows("58:59").Hidden = True
Rows("70:71").Hidden = True
Rows("82:83").Hidden = True
Rows("94:95").Hidden = True
ElseIf Range("$F$1").Value = "9" Then
Rows("17").Hidden = True
Rows("47").Hidden = True
Rows("59").Hidden = True
Rows("71").Hidden = True
Rows("83").Hidden = True
Rows("95").Hidden = True
ElseIf Range("$F$1").Value = "0" Then
Rows("9:101").Hidden = False
End If
Application.EnableEvents = True
End Sub
Sheet9:
Code:
Private Sub worksheet_calculate()
Static OldVal As Variant
If Range("$QC$1").Value <> OldVal Then
OldVal = Range("$QC$1").Value
Call RowHider
End If
End Sub
Sub RowHider()
Application.EnableEvents = False 'Disables events to try to allow Excel to run faster
If Range("$QC$1").Value = "1" Then
Rows("6:14").Hidden = True
ElseIf Range("$QC$1").Value = "2" Then
Rows("7:14").Hidden = True
ElseIf Range("$QC$1").Value = "3" Then
Rows("8:14").Hidden = True
ElseIf Range("$QC$1").Value = "4" Then
Rows("9:14").Hidden = True
ElseIf Range("$QC$1").Value = "5" Then
Rows("10:14").Hidden = True
ElseIf Range("$QC$1").Value = "6" Then
Rows("11:14").Hidden = True
ElseIf Range("$QC$1").Value = "7" Then
Rows("12:14").Hidden = True
ElseIf Range("$QC$1").Value = "8" Then
Rows("13:14").Hidden = True
ElseIf Range("$QC$1").Value = "9" Then
Rows("14").Hidden = True
ElseIf Range("$QC$1").Value = "10" Then
Rows("5:14").Hidden = False
ElseIf Range("$QC$1").Value = "0" Then
Rows("5:14").Hidden = False
End If
Application.EnableEvents = True 'Enables events to put Excel back to normal
End Sub
You can see in Sheet9 that I tried to add some code that would, hopefully, speed things up (though it seems to have done nothing).
I'd appreciate any help or tips. If anything in my post is formatted incorrectly, I'll fix whatever anyone points out.
Thank you to everyone just for looking at this!
Last edited by a moderator: