VBA code slowing down workbook

D

Dakar82

New Member
Joined
Jun 12, 2018
Messages
4
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:
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
Sheet6:
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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Thanks to anyone that looked this over, I found the culprit, and it wasn't my code. I had an add-in that is required for some of my work that slows down any of my files with code on my computer but no one else's, looks like it's time for new hardware!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
443
rahildhody
R
R
  • Solved
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
2
Replies
11
Views
674
Rnkhch
R
A
  • Question Question
VBA script delete row containing specific text
Replies
1
Views
764
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top