Error when selecting last row

**therealjag** · 10-07-2009, 07:20 AM

Hey there,

I am trying to delete all the data on "Sheet1" of my spreadsheet except row 1 but using the code below, I keep getting an error when the macro is trying to calculate the end row and column, can any tell me where I am going wrong...and to stop me from going bonkers?!

Sub clear_sheet_content()

Dim oSheet As Excel.Worksheet
Dim col As Integer
Dim row As Integer

Set oSheet = Worksheets("Sheet1")

col = ActiveSheet.UsedRange.Columns.count
row = ActiveSheet.UsedRange.Rows.count + ActiveSheet.UsedRange.row - 1

    On Error GoTo Errhandler
    
    oSheet.Range(cells(2, 1), cells(row, col)).Select
    Selection.ClearContents
    Selection.NumberFormat = "General"
    MsgBox "The sheet has been cleared", vbInformation, "Add Non Conformity"
    
Errhandler:

    MsgBox "There is no data on sheet1", vbInformation, "Add Non Conformity"
    
    
End Sub

Cheers,

Jag

**StephenR** · 10-07-2009, 07:37 AM

When you run the macro, which sheet is active and which sheet do you want cleared...?

**therealjag** · 10-07-2009, 07:49 AM

Hey Steve, I'm using that button on the NC Sheet Info to activate the macro. The macro should then clear all the data on "Sheet1" except of course the top row which is my header row.

**StephenR** · 10-07-2009, 07:50 AM

I was gently trying to point out that the macro calculates the extent of the data based on the active sheet, which is not the sheet containing the data when you press the button. Change references to ActiveSheet to oSheet.

EDIT: try this

Sub clear_sheet_content()

Dim oSheet As Excel.Worksheet
Dim col As Long
Dim row As Long

Set oSheet = Worksheets("Sheet1")

With oSheet
    col = .UsedRange.Columns.Count
    row = .UsedRange.Rows.Count + .UsedRange.row - 1
    
    On Error GoTo Errhandler
    With .Range(.Cells(2, 1), .Cells(row, col))
        .ClearContents
        .NumberFormat = "General"
    End With
End With
    MsgBox "The sheet has been cleared", vbInformation, "Add Non Conformity"
    Exit Sub
Errhandler:
    MsgBox "There is no data on sheet1", vbInformation, "Add Non Conformity"
    
End Sub

**therealjag** · 10-07-2009, 07:59 AM

Oh I see...yeah I've just amended that again actaully and it works! I really need to get a grip of using with and end with statements so I'll look into that now...thanks for your help