Clearing sheet1...

[Butane]

Im trying to clear all the data from an excel sheet. Im probably being stupid but i cant seem to get it right. It works if there is data, but somethimes if the sheet has no data it gives me an error. Is this the right code?

Worksheets("Sheet1&quot.Range("A1100&quot.Clear

Im not trying to clear just these specific cells, im trying to make all the cells in the whole sheet blank.

Thanks.
Henry

 

[dsi]

Try this:

Sheets("Sheet1").Range("A1:Z1000").ClearContents

 

[RobBroekhuis]

Or, even more generally,

sheets("sheet1&quot.usedrange.clear (or .clearcontents)

Use the clear method if you want everything including formats cleared; use clearcontents if you want just the formulas and values cleared.
Rob

 

[acron]

Use the following for the active sheet and the used range will also be reset

Sub ClearSheet()
Cells.Clear
ActiveSheet.UsedRange
End Sub

 

[RobBroekhuis]

Acron,
That's interesting! In a quick test, I found that the cells.clear method by itself resets the usedrange property - are there circumstances in which this does not happen?
Also, I could not find help on using UsedRange in the way you use it - is there a hidden default method associated with the property?
Rob

 

[Butane]

The '.ClearContents' method seems to work ok. Thanks guys.

 

[acron]

Rob,

I find Cells.Clear does not always reset the UsedRange to A1, which is what it should do. Tray it with some data in a sheet, press Ctrl-End and your cursor should not move outside A1.

Any use of UsedRange seems to make Excel wake up and re-calculate the last cell.

ClearContents will not work as good as Clear, unless of course you want to retain formatting etc.