Duplicating macros across worksheets

[Hoosier03]

I am using Excel 2007. I ran and recorded a simple marco that deletes data out of 10 sections in one workheet. I would like to have a macro that deletes the six other worksheets as well. How can I set this up to where I don't have to run it 6 times? Also, I cannot find the code in the VBE. Where I can find this?


Thanks,


Rodney

[Hoosier03]

Here is the code


Sub DataWipe()
'
' DataWipe Macro
'
'
Range("CA5:DZ17").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 65
Range("CA21:DZ33").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 123
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 120
ActiveWindow.ScrollColumn = 118
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
Range("CA37:DZ49").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 120
ActiveWindow.ScrollColumn = 119
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
Range("CA53:DZ65").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.SmallScroll Down:=27
Range("CA69:DZ81").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.SmallScroll Down:=21
Range("CA86:DZ98").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.SmallScroll Down:=18
Range("CA102:DZ114").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.SmallScroll Down:=24
Range("CA118:DZ130").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
Range("CA134:DZ146").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 118
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 64
ActiveWindow.SmallScroll Down:=30
Range("CA150:DZ162").Select
Selection.ClearContents
End Sub

[Hoosier03]

How can I clean this up and get it to run across multiple sheets at the same time?


Thanks,

Rodney

[Paul_Hossler]

1. Using the VBA tags makes it easier to read

2. The macro recorder will make executable code, but not effecient of flexible code

3. This is one way. It's pretty brute force, but has the advantages of being easy to read and to maintain

4. The macro recorder probably saved the code in PERSONAL.XLSB, but in your workbook, goto the VBE and insert a Module and paste this in to try



Option Explicit

Sub DataWipe()

Application.ScreenUpdating = False

Call DeleteRanges("Sheet1")
Call DeleteRanges("Sheet2")
Call DeleteRanges("Sheet3")
Call DeleteRanges("Sheet4")
Call DeleteRanges("Sheet5")

Application.ScreenUpdating = True
End Sub

Private Sub DeleteRanges(sWorksheet As String)
With ActiveWorkbook.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With

End Sub


This is not tested

Paul

[Hoosier03]

I inserted what you put and changed the sheet names to reflect my worksheets. I got an error message though with

With ActiveWorkbook.Worksheets(sWorksheet)

highlighted.



VBA

Option Explicit

Sub DataWipe()

Application.ScreenUpdating = False

Call DeleteRanges("Rev.")
Call DeleteRanges("COS")
Call DeleteRanges("Labor Dollars")
Call DeleteRanges("OE")
Call DeleteRanges("Labor Hours Hourly")
Call DeleteRanges("Labor Hours Salary")
Call DeleteRanges("Transfers")


Application.ScreenUpdating = True
End Sub

Private Sub DeleteRanges(sWorksheet As String)
With ActiveWorkbook.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With

End Sub

Thanks,

Rodney

[Hoosier03]

The error says "Subscript out of range"

[CatDaddy]

Sub DataWipe()

Application.ScreenUpdating = False

Dim ws as Worksheet

For each ws in ActiveWorkbook.Worksheets
ActiveWorkbook.ws.Activate
DeleteRanges
next ws

Application.ScreenUpdating = True
End Sub

Private Sub DeleteRanges()
With ActiveWorkbook.ActiveSheet
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With

End Sub

[Hoosier03]

CatDaddy,


I got an error on this one that says "Object won't support this property or method."

Also, will this work for specific worksheets? I don't want every worksheet to have the data deleted only the ones I listed (Rev, COS, Labor Dollars, etc.)

[CatDaddy]

no that will do all of the sheets...you could reference your sheets by numbers if the names arent working (from the code paul gave you)

[Hoosier03]

The names did not work. How do I assign them a number? Is is just the order they are in the workbook?

Paul's code gave me the "Subscript out of range" error message.

Thanks,

Rodney

[frank_m]

Pauls code runs fine for me.

"Subscript out of range" error occurs if one or more of the sheet names is spelled incorrectly within the code..

[Hoosier03]

I checked the names of the worksheets twice and they are correct. The error message highlights "With ActiveWorkbook.Worksheets(sWorksheet)" not the names of the worksheets.

[GTO]

Greetings Hoosier,

Barring that there are leading/trailing spaces in the actual strings on the workbook tabs, I would look at whether the correct workbook is active.

Just by example/for testing:

Option Explicit

Sub DataWipe()
Dim wb As Workbook, wkb As Workbook

Const FILE_NAME As String = "MyBook.xls"

For Each wb In Workbooks
On Error Resume Next
Set wkb = Workbooks(FILE_NAME)
On Error GoTo 0

If Not wkb Is Nothing Then

Call DeleteRanges("Rev.", wkb)
Call DeleteRanges("COS", wb)
Call DeleteRanges("Labor Dollars", wkb)
Call DeleteRanges("OE", wkb)
Call DeleteRanges("Labor Hours Hourly", wkb)
Call DeleteRanges("Labor Hours Salary", wkb)
Call DeleteRanges("Transfers", wkb)

Exit Sub
End If
Next

MsgBox "It does not appear that " & FILE_NAME & " is open.", vbCritical, vbNullString

End Sub

Private Sub DeleteRanges(sWorksheet As String, wb As Workbook)
With wb.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With

End Sub

Where did you put the code, in a Standard Module as Paul said?

If yes, is the code in the same workbook as the worksheets are?

[Hoosier03]

I entered everything you put in except I replaced "MyBook.xls" with the name of my file "Forecast Project.xlsm" I got the same error message.

This is the workbook that the worksheets are in. There are 14 worksheets in this workbook. The sheets listed are sheets 2 through 8. The code is in module 1 of the workbook that these worksheets are in.


Thanks,

Rodney

[CatDaddy]

yes the numbers start at 1 at the leftmost side and onward, should eliminate whatever spelling errors you are having

[Paul_Hossler]

Sorry you're having problems with my suggestions.

This has a little error catching. It might help track down the issue


Option Explicit

Sub DataWipe()

Application.ScreenUpdating = False

Call DeleteRanges("Sheet1")
Call DeleteRanges("Sheet2")
Call DeleteRanges("Sheet3")
Call DeleteRanges("Sheet4")
Call DeleteRanges("Sheet5")

Application.ScreenUpdating = True
End Sub

Private Sub DeleteRanges(sWorksheet As String)
Dim i As Long

i = -1
On Error Resume Next
i = ActiveWorkbook.Worksheets(sWorksheet).Index
On Error GoTo 0

If i = -1 Then
MsgBox "Sorry!!! --- '" & sWorksheet & "' does not exist"
Exit Sub
End If
With ActiveWorkbook.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With

End Sub

[Hoosier03]

Thanks Paul. That one actually runs with no error message, but it does not delete the data.

[GTO]

Thanks Paul. That one actually runs with no error message, but it does not delete the data.

Please attach your workbook with the code as you currently have it. Redact any sensitive info, but leave the sheet layout and code as they are.

[frank_m]

Those ranges seem a little backwards to me
It does still work for me in Excel 2003, my wild guess is that perhaps 2007 doesn't recognize those ranges.

Test
Range("CA5:Z17").Select 'Selects Range("Z5:CA17")<-is this the proper range to be cleared?

[GTO]

Those ranges seem a little backwards to me
It does still work for me in Excel 2003, my wild guess is that perhaps 2007 doesn't recognize those ranges.

Test
Range("CA5:Z17").Select 'Selects Range("Z5:CA17")<-is this the proper range to be cleared?

Nicely spotted:thumb

Hoosier:

Might this---> .Range("CA5:Z17").ClearContents
Supposed to be---> .Range("CA5:DZ17").ClearContents ???