You seem to be creating a multi-row range and trying to use that as the last
row. Try this

Private Sub Clear_Contents_Click()
With Worksheets("Compiled Totals")
.Range("$A$9:A" + .Cells(.Rows.Count,
"A").End(xlUp).Row).ClearContents
End With
With Worksheets("Upload Data")
.Range("$A$2:A" + .Cells(.Rows.Count,
"A").End(xlUp).Row).ClearContents
End With

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Connie" wrote in message
oups.com...
I'm using the following code to clear the contents of a selected range
on the "Compiled Totals" and "Upload Data" sheets. The code is called
from a command button which is on another sheet in the workbook:

Private Sub Clear_Contents_Click()

'Clear "Compiled Totals" and "Upload Data" sheets
Set sh = Worksheets("Compiled Totals")
Set rng = GetRealLastCell(sh)
Sheets("Compiled Totals").Range("$A$9:" + rng.Address).Select
Selection.ClearContents
Set sh = Worksheets("Upload Data")
Set rng = GetRealLastCell(sh)
Sheets("Upload Data").Range("$A$2:" + rng.Address).Select
Selection.ClearContents

End Sub

When I run the code, I get a 1004 error. What's wrong with my syntax?
Any help would be appreciated.

Connie