Make VBA Code Shorter

[klutz]

I have this code recorded in excel which simply selects and clears data in specified ranges. I want to know how can I make this shorter. I know selecting and clearing takes time to execute but i don't know any other way.



Sub Reset_All()
'
' Reset_All Macro
'
'
If MsgBox("You are about to reset the COMPLETE FORM, are you sure?" & Chr(10) & _
"Do you want to continue?", vbYesNoCancel) = vbYes Then

If MsgBox("Once form is cleared it can't be UNDONE, are you sure?" & Chr(10) & _
"Do you still wish to continue?", vbYesNoCancel) = vbYes Then
ActiveSheet.Unprotect Password:="PWD"
Selection.ClearContents
Range("E7:E17").Select
Selection.ClearContents
Range("N12:N13").Select
Selection.ClearContents
Range("M7").Select
Selection.ClearContents
Range("A2:A33").Select
Selection.ClearContents
Range("e24:L24").Select
Selection.ClearContents
Range("e27:L27").Select
Selection.ClearContents
Range("e29:L35").Select
Selection.ClearContents
Sheets("Allocations 2").Select
ActiveSheet.Unprotect Password:="PWD"

Range("BV25:BV300").Select
Selection.ClearContents
Range("BO25:BO300").Select
Selection.ClearContents
Range("BF25:BF300").Select
Selection.ClearContents
Range("BE25:BE300").Select
Selection.ClearContents
Range("AE25:AE300").Select
Selection.ClearContents
Range("AD25:AD300").Select
Selection.ClearContents
Range("AB25:AB300").Select
Selection.ClearContents
Range("S25:T300").Select
Selection.ClearContents
Range("I25:Q300").Select
Selection.ClearContents

Range("F6:F12").Select
Selection.ClearContents
Range("H7:H10").Select
Selection.ClearContents
Range("Ad18").Select
Selection.ClearContents
Range("Ad20").Select
Selection.ClearContents
Range("L8").Select
Selection.ClearContents
Range("L19").Select
Selection.ClearContents
Sheets("Work-In_Progress").Select
Range("B1").Select
ActiveSheet.Protect Password:="PWD", _
Contents:=True, _
AllowFormattingCells:=True

End If

End If

End Sub


Klutz---

[Bob Phillips]

Just don't do the selects.

So instead of



Range("E7:E17").Select
Selection.ClearContents


use



Range("E7:E17").ClearContents



and the same with the sheets, instead of



Sheets("Work-In_Progress").Select
Range("B1").Select
ActiveSheet.Protect Password:="PWD", _
Contents:=True, _
AllowFormattingCells:=True


use



Sheets("Work-In_Progress").Protect Password:="PWD", _
Contents:=True, _
AllowFormattingCells:=True

[Bob Phillips]

You can also do di-contiguous ranges in one hit, so instead of



Range("E7:E17").ClearContents
Range("N12:N13").ClearContents
Range("M7").ClearContents
Range("A2:A33").ClearContents
Range("e24:L24").ClearContents
Range("e27:L27").ClearContents
Range("e29:L35").ClearContents


use



Range("E7:E17,N12:N13,M7,A2:A33,E24:L24,E27:L27,E29:L35").ClearContents

[klutz]

XLD, thanks man, but unfortunate i have merged cells and the code breaks on it.

I'm gonna have too see if I can un-merge...

Thanks...

[Paul_Hossler]

1. Bit of a nit, but might avoid a little user confusion. You used



"Do you want to continue?", vbYesNoCancel) = vbYes Then

and
"Do you still wish to continue?", vbYesNoCancel) = vbYes Then

I'd use vbYesNo instead of vbYesNoCancel so people don't get confused between No and Cancel

2. I've made a lot of work for myself maintaining explicit Address references in code. Every time I change the form, I had to remember to carefully update addresses. If your form has the right kind of formatting, I like to let Excel do the work. For ex, shade the non-entry cells gray, and then just check the cells's (?) ColorIndex and to determine what to do. Could also check the .Locked status of the cell

Paul

[Bob Phillips]

XLD, thanks man, but unfortunate i have merged cells and the code breaks on it.

I'm gonna have too see if I can un-merge...

I have always said that merged cells are bad news.

[bruinenat]

Nice