klutz
10-09-2009, 05:12 PM
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---
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---