Rationalizing my VBA code (it hides/unhides rows and then autofits them)

**David M.** · 04-12-2014, 12:46 AM

Hi, I have a worksheet with content in rows 15-421. A user complained that that is too many rows to look through as she's really only interested in what is in about 70 of them. I therefore built a dropdown in E3 with the options being "Contract", which hides all but the 70ish rows, and "Expand", which displays them all. An additional bit of complexity is that, because many of the rows either do have or (after the user is through with them) will have a fair bit of content, the row height needs to autofit.

I wrote the code below, but I'm not very good with VBA so I'm not sure it's the most rational way to approach it. The "Expand" piece runs relatively smoothly, but the "Contract" piece is pretty herky-jerky. It does seem to get the job done, but if somebody can see a better way to approach this that will cause it to run a little more smoothly, I would definitely appreciate the advice.

Thank you!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("E3"), Target) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect
Rows("15:421").EntireRow.Hidden = False
Select Case Range("E3").Value
Case "Contract"
Rows("15:17").EntireRow.Hidden = True
Rows("19").EntireRow.Hidden = True
Rows("21:47").EntireRow.Hidden = True
Rows("49:51").EntireRow.Hidden = True
Rows("53:61").EntireRow.Hidden = True
Rows("64:68").EntireRow.Hidden = True
Rows("70:71").EntireRow.Hidden = True
Rows("74").EntireRow.Hidden = True
Rows("76:78").EntireRow.Hidden = True
Rows("80:95").EntireRow.Hidden = True
Rows("97").EntireRow.Hidden = True
Rows("99:104").EntireRow.Hidden = True
Rows("106:110").EntireRow.Hidden = True
Rows("112:120").EntireRow.Hidden = True
Rows("122:126").EntireRow.Hidden = True
Rows("135").EntireRow.Hidden = True
Rows("137").EntireRow.Hidden = True
Rows("139").EntireRow.Hidden = True
Rows("141").EntireRow.Hidden = True
Rows("143:148").EntireRow.Hidden = True
Rows("151:159").EntireRow.Hidden = True
Rows("161:165").EntireRow.Hidden = True
Rows("167:169").EntireRow.Hidden = True
Rows("173:179").EntireRow.Hidden = True
Rows("181:183").EntireRow.Hidden = True
Rows("185:204").EntireRow.Hidden = True
Rows("206:229").EntireRow.Hidden = True
Rows("231:232").EntireRow.Hidden = True
Rows("234:236").EntireRow.Hidden = True
Rows("239:240").EntireRow.Hidden = True
Rows("242").EntireRow.Hidden = True
Rows("245:248").EntireRow.Hidden = True
Rows("251").EntireRow.Hidden = True
Rows("253:279").EntireRow.Hidden = True
Rows("282:304").EntireRow.Hidden = True
Rows("308:331").EntireRow.Hidden = True
Rows("333:352").EntireRow.Hidden = True
Rows("355:359").EntireRow.Hidden = True
Rows("361:367").EntireRow.Hidden = True
Rows("370:382").EntireRow.Hidden = True
Rows("384:388").EntireRow.Hidden = True
Rows("390:392").EntireRow.Hidden = True
Rows("394").EntireRow.Hidden = True
Rows("396").EntireRow.Hidden = True
Rows("398:410").EntireRow.Hidden = True
Rows("413").EntireRow.Hidden = True
Rows("415:416").EntireRow.Hidden = True
Rows("419:421").EntireRow.Hidden = True
Case "Expand"
Rows("15:421").EntireRow.Hidden = False
Rows("15:421").Select
Selection.Rows.AutoFit
End Select
Range( _
"18:18,20:20,48:48,52:52,62:62,63:63,69:69,72:72,73:73,75:75,79:79,96:96,98:98,105:105,111:111,121:121,127:127,128:128,129:129,130:130,131:131" _
).Select
Range("A131").Activate
Union(Range( _
"170:170,171:171,172:172,180:180,184:184,18:18,20:20,48:48,52:52,62:62,63:63,69:69,72:72,73:73,75:75,79:79,96:96,98:98,105:105,111:111,121:121,127:127,128:128,129:129,130:130,131:131,132:132,133:133,134:134,136:136,138:138,140:140" _
), Range("142:142,149:149,150:150,160:160,166:166")).Select
Range("A184").Activate
Union(Range( _
"170:170,171:171,172:172,180:180,184:184,205:205,230:230,233:233,237:237,238:238,241:241,243:243,244:244,249:249,250:250,252:252,280:280,281:281,305:305,306:306,307:307,332:332,353:353,18:18,20:20,48:48,52:52,62:62,63:63,69:69,72:72,73:73" _
), Range( _
"75:75,79:79,96:96,98:98,105:105,111:111,121:121,127:127,128:128,129:129,130:130,131:131,132:132,133:133,134:134,136:136,138:138,140:140,142:142,149:149,150:150,160:160,166:166" _
)).Select
Range("A353").Activate
Union(Range( _
"170:170,171:171,172:172,180:180,184:184,205:205,230:230,233:233,237:237,238:238,241:241,243:243,244:244,249:249,250:250,252:252,280:280,281:281,305:305,306:306,307:307,332:332,353:353,354:354,360:360,368:368,369:369,383:383,389:389,393:393,395:395,397:397" _
), Range( _
"411:411,412:412,414:414,417:417,418:418,18:18,20:20,48:48,52:52,62:62,63:63,69:69,72:72,73:73,75:75,79:79,96:96,98:98,105:105,111:111,121:121,127:127,128:128,129:129,130:130,131:131,132:132,133:133,134:134,136:136,138:138,140:140" _
), Range("142:142,149:149,150:150,160:160,166:166")).Select
Range("A418").Activate
Union(Range( _
"170:170,171:171,172:172,180:180,184:184,205:205,230:230,233:233,237:237,238:238,241:241,243:243,244:244,249:249,250:250,252:252,280:280,281:281,305:305,306:306,307:307,332:332,353:353,354:354,360:360,368:368,369:369,383:383,389:389,393:393,395:395,397:397" _
), Range( _
"411:411,412:412,414:414,417:417,418:418,18:18,20:20,48:48,52:52,62:62,63:63,69:69,72:72,73:73,75:75,79:79,96:96,98:98,105:105,111:111,121:121,127:127,128:128,129:129,130:130,131:131,132:132,133:133,134:134,136:136,138:138,140:140" _
), Range("142:142,149:149,150:150,160:160,166:166")).EntireRow.AutoFit
Range("B12").Select
Me.Protect
Application.EnableEvents = True
End If
End Sub

**HaHoBe** · 04-12-2014, 12:52 AM

Hi, David M.,

you should wrap the procedure with code-tags according to Forum Rule #3.

Ciao,
Holger

**jmac1947** · 04-12-2014, 01:06 AM

Hello David, and welcome to the forums.

A few things to start with. Please take some time to read the forum rules to get a view on what is considered "good form". In particular the forum moderators take a dim view of posting code as raw text. If you select and copy your code from the posting above, click on the "#" icon and paste your code between the two tags it will make everybody happier and as a bonus will retain the formatting making it much easier to read.

The second thing to be aware of is that the people who provide most of the excellent advice are all volunteers who have real jobs. Sadly a post asking for some assistance that doesn't have a sample workbook attached will often get passed by, most of us don't have the time to create a copy of what we think you are trying to say. A sample should show enough data to clearly illustrate the before and after situation with expected results. Remember to sanitise the data to remove any confidential aspect.

Now to you question. Do you have control over the format of the worksheet, could you put in a "show/noshow" column and simply filter on that value?? to restrict what the user sees??

I am all for simple solutions as you can see

Just a quick thought. I think your next steps should be to wrap you code and then attach a sample workbook perhaps