Hi Bill,
I think it was unclear why you could not use Clear Contents,
One reason you can't use clear contents is because it does not touch formats (, or comments, or shapes)
Clear contents is the same as using the Del key
Instead you would want to use Edit, Clear, Clear All, though it will not clear shapes
that you might have also acquired from copying and pasting from a web page.
http://www.mvps.org/dmcritchie/excel/shapes.htm
Don't know what you are doing with preparations after bringing in Internet data, but
if you delete rows, I think you would still end up with #REF errors in your other worksheet(s).
The simplest would be to use the menus
Ctrl+A to select all cells
Edit, Clear, Clear Contents Del
Of course if you are using Excel 2003 you should "know" to
use Ctrl+Shift+SpaceBar instead of Ctrl+A
http://www.mvps.org/dmcritchie/excel/shortx2k.htm#foobar
The code that R.Venkataraman supplied is a single line of code to be included in a wrapper as a macro.
If you are not familiar with installing and using a macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Examples to clear a specific sheet: sheet1 or 'sheet one'
Sub clearsheet1()
sheet1.cells.clear 'changed to cells instead of usedrange
end sub
Sub clearsheetone()
Sheets("sheet one").cells.Clear
end sub
Since it is for a specific sheet, you could use an Event macro instead
http://www.mvps.org/dmcritchie/excel/event.htm
which would only apply to the sheet that it is in. It could have just the
one line, or you could make it a bit more accident proof.
Event macros are installed differently -- Install this Event macro as follows:
- right click on sheet tab
- view code
- place code after the line "option explicit"
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ans As String
If Target.Address(0, 0) <> "A1" Then
MsgBox "Double click must be from A1 to clear this sheet", _
vbOK, "Must Double click from A1 to Clear This Sheet"
Exit Sub
End If
ans = MsgBox("Press 'OK' to Clear this worksheet", _
vbOKCancel, "Verify Clear Contents, Comments, Formats")
If ans = vbCancel Then Exit Sub
ActiveSheet.Cells.Clear
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes
Selection.Delete '*** delete selected shapes OR cell seleection
End Sub
