Clear Data

[MFRASER]

How do I delete everything in a spreadsheet without deleting the
spreadsheet?

 

[Frank Kabel]

Hi
try
- CTRL + A
- DEL

 

[Bob Phillips]

Why do you not what to delete the spreadsheet? You can always New another.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

I like to add this to Frank's reply

If you use Ctrl-A in excel 2003 it will select the current region.
If you use it a second time it will select all cells.
For all versions you can click on the left of the column number A and above
the row number 1 to
select all cells

With code you can use

Sheets("sheet1").Clear

Or

Sheets("sheet1").ClearContents

 

[MFRASER]

Still not working, I am using Excel 2000. Here is my code to clear the data
but the messagebox still displays.
'I set the DataSheet before I call this code

DataSheet.Select
DataSheet.Cells.Clear
DataSheet.Clear
Dim arange As Range

Set arange = DataSheet.Cells.SpecialCells(xlCellTypeLastCell)
If arange.Column <> 1 Then
MsgBox ("Error")
End If

 

[MFRASER]

Code doesn't work for 2000. Any other ideas?

I like to add this to Frank's reply

If you use Ctrl-A in excel 2003 it will select the current region.
If you use it a second time it will select all cells.
For all versions you can click on the left of the column number A and above
the row number 1 to
select all cells

With code you can use

Sheets("sheet1").Clear

Or

Sheets("sheet1").ClearContents

 

[Ron de Bruin]

Oops

Sheets("sheet1").Cells.Clear

I forgot the cells

 

[Ron de Bruin]

Hi

Your usedrange is not reset without saving (close)

 

[MFRASER]

Still not working here is my code

DataSheet.Activate
DataSheet.Cells.ClearContents
Sheets(DataSheetTitle).Cells.ClearContents
Dim arange As Range



Set arange = DataSheet.Cells.SpecialCells(xlCellTypeLastCell)
If arange.Column <> 1 Then
MsgBox ("Error")
End If

 

[Fraser Michael]

The reason I do not want to delete the spreadsheet is that I reuse the
spreadsheet for new tables. Here is what I am doing I am creating a
pivot table based on a recordset for each recordset I create a new
pivottable and a new spreadsheet. Before I was creating a _Data
spreadsheet for each pivottable and this gave me a MEMORY error. So I
thought by reusing the same spreadsheet I would fix the Memory error.

Thanks

 

[MFRASER]

Thank you.

Is there another way to get my last used Row and Last used Column?

 

[Ron de Bruin]

You can use this

Sub test()
MsgBox LastRow(ActiveSheet)
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function