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

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, _

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question