Clearing Used cells in Excel? 2

[BChumie]

Hello,

Is there any way to clear out all of the data from an excel spreadsheet using VBA. When I ask this question currently I am clearing a range of cells, but in order to clear the entire range of cells I have a HUGE range.

workbooks("book1.xls&quot.worksheets(1).range("A1:ag65000&quot.clear

I was wondering if there was a way to clear just the cells that have been used (there is no set number that is used, it varies from month to month). So if one month I use the range ("A1:AG3000&quot and the next month I use the range ("A1:AG60000&quot is there a way to clear out just the range i used for that month. Any help would be greatly appreciated.

Thanks,
Brad

 

[kevinclark]

If there are no cells containing data that you need to keep, just clear the entire sheet rather then worry about ranges.

Sheets("Sheet1&quot.Cells.ClearContents

 

[xlbo]

Or you could try:

Sheets("Sheet1&quot.UsedRange.Cells.clearcontents

you may need to add
colCount = sheets("Sheet1&quot.usedrange.columns.count
rowCount = sheets("Sheet1&quot.usedrange.rows.count

before the clearcontents statement just to make sure that the usedrange is accurate

HTH
Geoff

 

[BChumie]

Thanks to both of you for this information.

-Brad

 

[Loomah]

This one still freaks me out coz I just don't understand what I've done!!

However the code appears to select cells containing data but not formulae so it may be useful for clearing data. It also appears to select a true used range. Anyone who wants to test it fully is more than welcome!!!

Sub used_Range_ofsorts()
With ActiveSheet.Range("A1&quot.SpecialCells(xlEnd)
.Select
End With
' do something here eg selection.clearcontents
' though obviously it isn't necessary to ".Select"
End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?