Results 1 to 7 of 7

Trying to clean up this small code

Threaded View

  1. 05-14-2017, 12:19 PM #1
    g1terra
    g1terra is offline
    Forum Contributor
    Join Date
    06-02-2014
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    235

    Trying to clean up this small code

    Hello guys,

    I am just getting started in VBA and have had help building a worksheet. I need to clean up one of my codes. Can anyone help me with this one? it works but i know its very sloppy. Any help cleaning it up would be GREATLY APPRECIATED!!

    Sub Clear_ALL()
'
' Clear_ALL Macro

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

    Dim varResponse As Variant
     
    varResponse = MsgBox("BE CAREFUL!! CLEAR ENTIRE SCHEDULE?                                                                  This will clear all shifts only and will erase ALL shifts in each week of the period and cannot be undone! This will not clear manually added overwritten shift times (Select Yes or No)", vbYesNo, "Selection")
    If varResponse <> vbYes Then Exit Sub
     ' The rest of your code goes here
         
    Range("U13:AH13").Select
    Selection.ClearContents
    Range("U15:AH15").Select
    Selection.ClearContents
    Range("U17:AH17").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("U19:AH19").Select
    Selection.ClearContents
    Range("U21:AH21").Select
    Range("AG21").Activate
    Selection.ClearContents
    Range("U23:AH23").Select
    Selection.ClearContents
    Range("U25:AH25").Select
    Selection.ClearContents
    Range("U27:AH27").Select
    Selection.ClearContents
    Range("U29:AH29").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("U31:AH31").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-6
    Union(Range( _
        "AB20,AB18,AB16,AB14,AD14,AD16,AD18,AD20,AD22,AD24,AD26,AD28,AD30,AF30,AF28,AF26,AF24,AF22,AF20,AF18,AF16,AF14,AH14,AH16,AH18,AH20,AH22,AH24,AH26,AH28,AH30,V14" _
        ), Range( _
        "V16,V18,V20,V22,V24,V26,V28,V30,X30,X28,X26,X24,X22,X20,X18,X16,X14,Z14,Z16,Z18,Z20,Z22,Z24,Z26,Z28,Z30,AB30,AB28,AB26,AB24,AB22" _
        )).Select
    Range("AH30").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("V32,X32,Z32,AB32,AD32,AF32,AH32").Select
    Range("AH32").Activate
    Selection.ClearContents
    Range("U33:AH34").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=12
    Range("U36:AH36").Select
    Selection.ClearContents
    Range("U38:AH38").Select
    Selection.ClearContents
    Range("U40:AH40").Select
    Selection.ClearContents
    Range("U42:AH42").Select
    Selection.ClearContents
    Range("U44:AH44").Select
    Selection.ClearContents
    Range("U46:AH46").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=12
    Range("U48:AH59").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("U69:AH69").Select
    Selection.ClearContents
    Range("U71:AH71").Select
    Selection.ClearContents
    Range("U73:AH73").Select
    Selection.ClearContents
    Range("U75:AH75").Select
    Selection.ClearContents
    Range("U77:AH77").Select
    Selection.ClearContents
    Range("U79:AH79").Select
    Selection.ClearContents
    Range("U81:AH81").Select
    Selection.ClearContents
    Range("U83:AH83").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("U85:AH85").Select
    Selection.ClearContents
    Range("U87:AH87").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-6
    Union(Range( _
        "AB84,AB82,AB80,AB78,AB76,AB74,AB72,AB70,AD70,AD72,AD74,AD76,AD78,AD80,AD82,AD84,AD86,AD88,AF88,AF86,AF84,AF82,AF80,AF78,AF76,AF74,AF72,AF70,AH70,AH72,AH74,AH76" _
        ), Range( _
        "AH78,AH80,AH82,AH84,AH86,AH88,V70,V72,V74,V76,V78,V80,V82,V84,V86,V88,X88,X86,X84,X82,X80,X78,X76,X74,X72,X70,Z70,Z72,Z74,Z76,Z78,Z80" _
        ), Range("Z82,Z84,Z86,Z88,AB88,AB86")).Select
    Range("AH88").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=18
    Range("U92:AH92").Select
    Selection.ClearContents
    Range("U94:AH94").Select
    Selection.ClearContents
    Range("U96:AH96").Select
    Selection.ClearContents
    Range("U98:AH98").Select
    Selection.ClearContents
    Range("U100:AH100").Select
    Selection.ClearContents
    Range("U102:AH102").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=12
    Range("U104:AH115").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("U125:AH125").Select
    Selection.ClearContents
    Range("U127:AH127").Select
    Selection.ClearContents
    Range("U129:AH129").Select
    Selection.ClearContents
    Range("U131:AH131").Select
    Selection.ClearContents
    Range("U133:AH133").Select
    Selection.ClearContents
    Range("U135:AH135").Select
    Selection.ClearContents
    Range("U137:AH137").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("U139:AH140").Select
    Range("U139:AH139").Select
    Selection.ClearContents
    Range("U141:AH141").Select
    Selection.ClearContents
    Range("U143:AH143").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=0
    Union(Range( _
        "AB140,AB138,AB136,AB134,AB132,AB130,AB128,AB126,AD126,AD128,AD130,AD132,AD134,AD136,AD138,AD140,AD142,AD144,AF144,AF142,AF140,AF138,AF136,AF134,AF132,AF130,AF128,AF126,AH126,AH128,AH130,AH132" _
        ), Range( _
        "AH134,AH136,AH138,AH140,AH142,AH144,V126,V128,V130,V132,V134,V136,V138,V140,V142,V144,X144,X142,X140,X138,X136,X134,X132,X130,X128,X126,Z126,Z128,Z130,Z132,Z134,Z136" _
        ), Range("Z138,Z140,Z142,Z144,AB144,AB142")).Select
    Range("AH144").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("U145:AH146").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("U148:AH148").Select
    Selection.ClearContents
    Range("U150:AH150").Select
    Selection.ClearContents
    Range("U152:AH152").Select
    Selection.ClearContents
    Range("U154:AH154").Select
    Selection.ClearContents
    Range("U156:AH156").Select
    Selection.ClearContents
    Range("U158:AH158").Select
    Selection.ClearContents
    Range("U160:AH171").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("U181:AH181").Select
    Selection.ClearContents
    Range("U183:AH183").Select
    Selection.ClearContents
    Range("U185:AH185").Select
    Selection.ClearContents
    Range("U187:AH187").Select
    Selection.ClearContents
    Range("U189:AH189").Select
    Selection.ClearContents
    Range("U191:AH191").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("U193:AH193").Select
    Selection.ClearContents
    Range("U195:AH195").Select
    Selection.ClearContents
    Range("U197:AH197").Select
    Selection.ClearContents
    Range("U199:AH199").Select
    Selection.ClearContents
    Union(Range( _
        "AB186,AB188,AB190,AB192,AB194,AB196,AB198,AB200,AD200,AD198,AD196,AD194,AD192,AD190,AD188,AD186,AD184,AD182,AF182,AF184,AF186,AF188,AF190,AF192,AF194,AF196,AF198,AF200,AH182,AH184,AH186,AH188" _
        ), Range( _
        "AH190,AH192,AH194,AH196,AH198,AH200,V200,V198,V196,V194,V192,V190,V188,V186,V184,V182,X182,X184,X186,X188,X190,X192,X194,X196,X198,X200,Z200,Z198,Z196,Z194,Z192,Z190" _
        ), Range("Z188,Z186,Z184,Z182,AB182,AB184")).Select
    Range("AH200").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=9
    Range("U201:AH202").Select
    Selection.ClearContents
    Range("U204:AH204").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("U206:AH206").Select
    Selection.ClearContents
    Range("U208:AH208").Select
    Selection.ClearContents
    Range("U210:AH210").Select
    Selection.ClearContents
    Range("U212:AH212").Select
    Selection.ClearContents
    Range("U214:AH214").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("U216:AH227").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 203
    ActiveWindow.ScrollRow = 197
    ActiveWindow.ScrollRow = 192
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 180
    ActiveWindow.ScrollRow = 172
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=-3
    Range("U237:AH237").Select
    Selection.ClearContents
    Range("U239:AH239").Select
    Selection.ClearContents
    Range("U241:AH241").Select
    Selection.ClearContents
    Range("U243:AH243").Select
    Selection.ClearContents
    Range("U245:AH245").Select
    Selection.ClearContents
    Range("U247:AH247").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=3
    Range("U249:AH249").Select
    Selection.ClearContents
    Range("U251:AH251").Select
    Selection.ClearContents
    Range("U253:AH253").Select
    Selection.ClearContents
    Range("U255:AH255").Select
    Selection.ClearContents
    Union(Range( _
        "AB252,AB250,AB248,AB246,AB244,AB242,AB240,AB238,AD238,AD240,AD242,AD244,AD246,AD248,AD250,AD252,AD254,AD256,AF238,AF240,AF242,AF244,AF246,AF248,AF250,AF252,AF254,AF256,AH256,AH254,AH252,AH250" _
        ), Range( _
        "AH248,AH246,AH244,AH242,AH240,AH238,V238,V240,V242,V244,V246,V248,V250,V252,V254,V256,X256,X254,X252,X250,X248,X246,X244,X242,X240,X238,Z238,Z240,Z242,Z244,Z246,Z248" _
        ), Range("Z250,Z252,Z254,Z256,AB256,AB254")).Select
    Range("AH238").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=15
    Range("U257:AH258").Select
    Selection.ClearContents
    Range("U260:AH260").Select
    Selection.ClearContents
    Range("U262:AH262").Select
    Selection.ClearContents
    Range("U264:AH264").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=6
    Range("U266:AH266").Select
    Selection.ClearContents
    Range("U268:AH268").Select
    Selection.ClearContents
    Range("U270:AH270").Select
    Selection.ClearContents
    Range("U272:AH283").Select
    Selection.ClearContents
    ActiveWindow.ScrollRow = 265
    ActiveWindow.ScrollRow = 262
    ActiveWindow.ScrollRow = 258
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 246
    ActiveWindow.ScrollRow = 237
    ActiveWindow.ScrollRow = 227
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 1
    Range("J6:O6").Select



Application.Goto rng
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
MsgBox "Schedule cleared!"

End Sub
    Last edited by JBeaucaire; 05-14-2017 at 12:26 PM. Reason: Added missing CODE tags, please read the Forum Rules, link above in the menu bar. Thanks.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code clean up
    By DHHM in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2015, 03:41 AM
  2. Have VBA code that needs performed in multiple sheets. Also help being sure code is clean
    By baby_kay_2003 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-17-2014, 10:57 PM
  3. Code clean up
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2012, 07:17 PM
  4. [SOLVED] code clean up
    By promoboy2 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-03-2012, 06:06 PM
  5. [SOLVED] Clean up code
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2012, 03:18 PM
  6. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1