Excel 2003 button issue

[Neil Holden]

hi all, i have a button and when pressed it copies sheets to another external
workbook, the problem i'm having is when i need it to clear the content it
clears the header row. I need it to keep the header row but delete the
content.

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\Business Objects\CHR\Export of SGUK.xls")
Set ws = wb2.Sheets("Sheet1")
ws.Cells.ClearContents 'i think this is where i'm going wrong.

Help would be greatly appreciated.

 

[Jacob Skaria]

One way is to use the usedrange and define the range starting from cell A2...

ws.Range("A2", Cells(ws.UsedRange.Rows.Count, _
ws.UsedRange.Columns.Count)).ClearContents

 

[Dave Peterson]

Another way:

With ws
.Range("a2", .Cells(.Cells.Count)).ClearContents
End With

..cells(.cells.count) is the last cell (IV65536 in xl2003) in the worksheet.