Clean up code

**g1terra** · 05-16-2017, 03:34 AM

Can someone help me clean up the bottom of this code? Someone helped me on an older version but I had to modify it. I started following the same philosophy by removing the Select, Selections but I'm stuck after that?

Sub Clear_Sched()
'
' Clear_Sched Macro
'

Set rng = ActiveCell
Application.ScreenUpdating = False
ActiveSheet.Unprotect

Dim varResponse As Variant

varResponse = MsgBox("< BE CAREFUL!! > CLEAR ENTIRE SCHEDULE? This will clear the entire period and will erase ALL shifts in each week and cannot be undone! (This will NOT clear overwritten shift times) < Select YES or NO >", vbYesNo, "Selection")
If varResponse <> vbYes Then Exit Sub
' The rest of your code goes here

Range("T8:AG11").ClearContents
Range("T13:AG13").ClearContents
Range("T15:AG15").ClearContents
Range("T17:AG17").ClearContents
Range("T19:AG19").ClearContents
Range("T21:AG21").ClearContents
Range("T23:AG23").ClearContents
Range("T25:AG25").ClearContents
Range("T27:AG27").ClearContents
Range("T29:AG29").ClearContents
Range("T31:AG31").ClearContents
Range("T33:AG34").ClearContents
Range("T36:AG36").ClearContents
Range("T38:AG38").ClearContents
Range("T40:AG40").ClearContents
Range("T42:AG42").ClearContents
Range("T44:AG44").ClearContents
Range("T46:AG46").ClearContents
Range("T48:AG49").ClearContents
Range("T51:AG56").ClearContents
Range("T58:AG59").ClearContents
Range("T64:AG67").ClearContents
Range("T69:AG69").ClearContents
Range("T71:AG71").ClearContents
Range("T73:AG73").ClearContents
Range("T75:AG75").ClearContents
Range("T77:AG77").ClearContents
Range("T79:AG79").ClearContents
Range("T81:AG81").ClearContents
Range("T83:AG83").ClearContents
Range("T85:AG85").ClearContents
Range("T87:AG87").ClearContents
Range("T89:AG90").ClearContents
Range("T92:AG92").ClearContents
Range("T94:AG94").ClearContents
Range("T96:AG96").ClearContents
Range("T98:AG98").ClearContents
Range("T100:AG100").ClearContents
Range("T102:AG102").ClearContents
Range("T104:AG105").ClearContents
Range("T107:AG112").ClearContents
Range("T114:AG115").ClearContents
Range("T120:AG123").ClearContents
Range("T125:AG125").ClearContents
Range("T127:AG127").ClearContents
Range("T129:AG129").ClearContents
Range("T131:AG131").ClearContents
Range("T133:AG133").ClearContents
Range("T135:AG135").ClearContents
Range("T137:AG137").ClearContents
Range("T139:AG139").ClearContents
Range("T141:AG141").ClearContents
Range("T143:AG143").ClearContents
Range("T145:AG146").ClearContents
Range("T148:AG148").ClearContents
Range("T150:AG150").ClearContents
Range("T152:AG152").ClearContents
Range("T154:AG154").ClearContents
Range("T156:AG156").ClearContents
Range("T158:AG158").ClearContents
Range("T160:AG161").ClearContents
Range("T163:AG168").ClearContents
Range("T170:AG171").ClearContents
Range("T176:AG179").ClearContents
Range("T181:AG181").ClearContents
Range("T183:AG183").ClearContents
Range("T185:AG185").ClearContents
Range("T187:AG187").ClearContents
Range("T189:AG189").ClearContents
Range("T191:AG191").ClearContents
Range("T193:AG193").ClearContents
Range("T201:AG201").ClearContents
Range("T199:AG199").ClearContents
Range("T197:AG197").ClearContents
Range("T195:AG195").ClearContents
Range("T193:AG193").ClearContents
Range("T191:AG191").ClearContents
Range("T189:AG189").ClearContents
Range("T187:AG187").ClearContents
Range("T185:AG185").ClearContents
Range("T183:AG183").ClearContents
Range("T181:AG181").ClearContents
Range("T201:AG217").ClearContents
Range("T219:AG224").ClearContents
Range("T226:AG227").ClearContents
Range("T237:AG237").ClearContents
Range("T232:AG235").ClearContents
Range("T239:AG239").ClearContents
Range("T241:AG241").ClearContents
Range("T243:AG243").ClearContents
Range("T245:AG245").ClearContents
Range("T247:AG247").ClearContents
Range("T249:AG249").ClearContents
Range("T251:AG251").ClearContents
Range("T253:AG253").ClearContents
Range("T255:AG255").ClearContents
Range("T257:AG258").ClearContents
Range("T260:AG260").ClearContents
Range("T262:AG262").ClearContents
Range("T264:AG264").ClearContents
Range("T266:AG266").ClearContents
Range("T268:AG268").ClearContents
Range("T270:AG270").ClearContents
Range("T272:AG283").ClearContents
ActiveWindow.SmallScroll Down:=-297
Range( _
"U14,W14,Y14,AA14,AC14,AE14,AG14,AG16,AE16,AC16,AA16,Y16,W16,U16,U18,W18,Y18,AA18,AC18,AE18,AG18" _
).Select
Range("AG18").Activate
Union(Range( _
"W26,W24,W22,Y20,Y22,Y24,Y26,Y28,Y30,Y32,AA32,AA30,AA28,AA26,AA24,AA22,AA20,AC20,AC22,AC24,AC26,AC28,AC30,AC32,AE32,AE30,AE28,AE26,AE24,AE22,AE20,AG20" _
), Range( _
"AG22,AG24,AG26,AG28,AG30,AG32,U14,W14,Y14,AA14,AC14,AE14,AG14,AG16,AE16,AC16,AA16,Y16,W16,U16,U18,W18,Y18,AA18,AC18,AE18,AG18,U20,W20,U22,U24,U26" _
), Range("U28,U30,U32,W32,W30,W28")).Select
Range("AG32").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=6
Selection.ClearContents
ActiveWindow.SmallScroll Down:=6
Range("T37:U37").Select
ActiveWindow.SmallScroll Down:=42
Union(Range( _
"AA76,AA74,AA72,AA70,AC70,AC72,AC74,AC76,AC78,AC80,AC82,AC84,AC86,AE86,AE84,AE82,AE80,AE78,AE76,AE74,AE72,AE70,U70,U72,U74,U76,U78,U80,U82,U84,U86,W86" _
), Range( _
"W84,W82,W80,W78,W76,W74,W72,W70,Y70,Y72,Y74,Y76,Y78,Y80,Y82,Y84,Y86,AA86,AA84,AA82,AA80,AA78" _
)).Select
Range("AE70").Activate
ActiveWindow.SmallScroll Down:=5
Union(Range( _
"AA76,AA74,AA72,AA70,AC70,AC72,AC74,AC76,AC78,AC80,AC82,AC84,AC86,AE86,AE84,AE82,AE80,AE78,AE76,AE74,AE72,AE70,U88,W88,Y88,AA88,AC88,AE88,AG88,AG86,AG84,AG82" _
), Range( _
"AG80,AG78,AG76,AG74,U70,U72,U74,U76,U78,U80,U82,U84,U86,W86,W84,W82,W80,W78,W76,W74,W72,W70,Y70,Y72,Y74,Y76,Y78,Y80,Y82,Y84,Y86,AA86" _
), Range("AA84,AA82,AA80,AA78")).Select
Range("AG74").Activate
ActiveWindow.SmallScroll Down:=-2
Union(Range( _
"AA76,AA74,AA72,AA70,AC70,AC72,AC74,AC76,AC78,AC80,AC82,AC84,AC86,AE86,AE84,AE82,AE80,AE78,AE76,AE74,AE72,AE70,U88,W88,Y88,AA88,AC88,AE88,AG88,AG86,AG84,AG82" _
), Range( _
"AG80,AG78,AG76,AG74,AG72,U70,U72,U74,U76,U78,U80,U82,U84,U86,W86,W84,W82,W80,W78,W76,W74,W72,W70,Y70,Y72,Y74,Y76,Y78,Y80,Y82,Y84,Y86" _
), Range("AA86,AA84,AA82,AA80,AA78")).Select
Range("AG72").Activate
ActiveWindow.SmallScroll Down:=-2
Union(Range( _
"AA76,AA74,AA72,AA70,AC70,AC72,AC74,AC76,AC78,AC80,AC82,AC84,AC86,AE86,AE84,AE82,AE80,AE78,AE76,AE74,AE72,AE70,U88,W88,Y88,AA88,AC88,AE88,AG88,AG86,AG84,AG82" _
), Range( _
"AG80,AG78,AG76,AG74,AG72,AG70,U70,U72,U74,U76,U78,U80,U82,U84,U86,W86,W84,W82,W80,W78,W76,W74,W72,W70,Y70,Y72,Y74,Y76,Y78,Y80,Y82,Y84" _
), Range("Y86,AA86,AA84,AA82,AA80,AA78")).Select
Range("AG70").Activate
ActiveWindow.SmallScroll Down:=-2
Selection.ClearContents
ActiveWindow.SmallScroll Down:=54
Union(Range( _
"AC134,AC136,AC138,AE138,AE136,AE134,AE132,AE130,AE128,AE126,AG126,AG128,AG130,AF133:AG133,AG132,AF135:AG135,AG136,AG138,U126,U128,U130,U132,U134,U136,U138,W138,W136,W134,W132,W130,W128,W126" _
), Range( _
"Y126,Y128,Y130,Y132,Y134,Y136,Y138,AA138,AA136,AA134,AA132,AA130,AA128,AA126,AC126,AC128,AC130,AC132" _
)).Select
Range("AG138").Activate
ActiveWindow.SmallScroll Down:=8
Union(Range( _
"AC134,AC136,AC138,AE138,AE136,AE134,AE132,AE130,AE128,AE126,AG126,AG128,AG130,AF133:AG133,AG132,AF135:AG135,AG136,AG138,U140,W140,Y140,AA140,AC140,AE140,AE142,AE144,AG144,AG142,AG140,AC142,AC144,AA144" _
), Range( _
"AA142,Y142,Y144,W144,W142,U142,U144,U126,U128,U130,U132,U134,U136,U138,W138,W136,W134,W132,W130,W128,W126,Y126,Y128,Y130,Y132,Y134,Y136,Y138,AA138,AA136,AA134,AA132" _
), Range("AA130,AA128,AA126,AC126,AC128,AC130,AC132")).Select
Range("U144").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=51
Union(Range( _
"AA188,AA186,AA184,AA182,AC182,AC184,AC186,AC188,AC190,AC192,AC194,AC196,AC198,AE198,AE196,AE194,AE192,AE190,AE188,AE186,AE184,AE182,AG182,AG184,AG186,AG188,AG190,AG192,AG194,AG196,AG198,U182" _
), Range( _
"U184,U186,U188,U190,U192,U194,U196,U198,W198,W196,W194,W192,W190,W188,W186,W184,W182,Y182,Y184,Y186,Y188,Y190,Y192,Y194,Y196,Y198,AA198,AA196,AA194,AA192,AA190" _
)).Select
Range("AG198").Activate
ActiveWindow.SmallScroll Down:=7
Union(Range( _
"AA188,AA186,AA184,AA182,AC182,AC184,AC186,AC188,AC190,AC192,AC194,AC196,AC198,AE198,AE196,AE194,AE192,AE190,AE188,AE186,AE184,AE182,AG182,AG184,AG186,AG188,AG190,AG192,AG194,AG196,AG198,U200" _
), Range( _
"W200,Y200,AA200,AC200,AE200,AG200,U182,U184,U186,U188,U190,U192,U194,U196,U198,W198,W196,W194,W192,W190,W188,W186,W184,W182,Y182,Y184,Y186,Y188,Y190,Y192,Y194,Y196" _
), Range("Y198,AA198,AA196,AA194,AA192,AA190")).Select
Range("AG200").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=49
Range( _
"U238,U240,U242,U244,U246,U248,U250,U252,U254,W254,W252,W250,W248,W246,W244,W242,W240,W238,Y238,Y240,Y242,Y244,Y246,Y248,Y250,X252:Y253,Y254,Y252,X252,AA254" _
).Select
Range("AA254").Activate
Selection.ClearContents
Range("Z252").Select
Selection.Copy
Range("X252").Select
ActiveSheet.Paste
Range("AA252").Select
Application.CutCopyMode = False
Union(Range( _
"AG252,AG254,AA252,AA250,AA248,AA246,AA244,AA242,AA240,AA238,AC238,AC240,AC242,AC244,AC246,AC248,AC250,AC252,AE252,AE250,AE248,AE246,AE244,AE242,AE240,AE238,AG238,AG240,AF243:AG243,AG244,AG242,AG246" _
), Range("AG248,AG250")).Select
Range("AG254").Activate
ActiveWindow.SmallScroll Down:=11
Union(Range( _
"AG252,AG254,U254,U256,W256,W254,Y254,Y256,AA256,AA254,AC254,AC256,AE256,AE254,AG256,AA252,AA250,AA248,AA246,AA244,AA242,AA240,AA238,AC238,AC240,AC242,AC244,AC246,AC248,AC250,AC252,AE252" _
), Range( _
"AE250,AE248,AE246,AE244,AE242,AE240,AE238,AG238,AG240,AF243:AG243,AG244,AG242,AG246,AG248,AG250" _
)).Select
Range("AG256").ClearContents

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Application.ScreenUpdating = True
Application.Goto rng
MsgBox "Schedule cleared!"

End Sub

**sintek** · 05-16-2017, 03:40 AM

Hi G1Terra
In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

**TMS** · 05-16-2017, 03:52 AM

And, when you add your Code Tags, it's probably worth adding a sample workbook with some typical data so we can see what you are trying to clear.

**g1terra** · 05-16-2017, 05:02 AM

I am sorry I am just a novice at VBA and posting code. I hope this is correct now. I am clear specific cells on the worksheet.

Please Login or Register  to view this content.

**TMS** · 05-16-2017, 05:51 AM

I'm thinking this does the job:

Please Login or Register  to view this content.

**sintek** · 05-16-2017, 06:32 AM

@TMS

A lot of OP sheets formulas are being cleared....

**TMS** · 05-16-2017, 08:38 AM

@sintek: oops, thank you. Saw the DV but didn't notice the formulae.

Take 2:

Please Login or Register  to view this content.

**TMS** · 05-16-2017, 08:43 AM

Take 3 ... added Application.ScreenUpdating = True/False:

Please Login or Register  to view this content.

**g1terra** · 05-17-2017, 12:41 AM

Wow, That's a cool trick! When it added the X's in the box it actually clear the formulas I need to keep on some of them. I highlighted in BLUE the formulas that need to be kept when clearing the constants. I am attaching the worksheet again. Is it possible to not clear those formulas? I created a module in this worksheet with your code. Called (Clear_Fill)

**g1terra** · 05-17-2017, 01:20 AM

Actually cancel that. I didn't have formulas in there. I just copied them over and tested your code. ITS AMAZING!! I'm going to test it out this week at work. Thank you so much!!

Clean up code

LinkBack

Thread Tools

Rate This Thread

Display

Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Re: Clean up code

Thread Information

Users Browsing this Thread

Similar Threads

Have VBA code that needs performed in multiple sheets. Also help being sure code is clean

Clean up code

Code clean up

[SOLVED] code clean up

[SOLVED] Clean up code

[SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol

Bookmarks

Bookmarks

Posting Permissions