Excel 2003 button issue

  • Thread starter Thread starter Neil Holden
  • Start date Start date
N

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.
 
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
 
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.
 
Back
Top