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

--
Jacob


"Neil Holden" wrote:

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.