#REF! help

I copy and paste a web page into excel in sheet1
The page I import changes every day and is different
in size , I delete all data in sheet 1 and copy and paste
the new web page in sheet 1.
I cannot use "clear contents " as the new web page size is
different to the last web page.
I have all my work sheet function formulas to extract data
from sheet 1 in sheet 2.When I delete sheet 1, I get #REF!
errors in all my formulas in sheet 2.
Is ther a way to over come this?
I would like a work sheet function if there is one

Thanks for all reply's

regards Bill
--
bill gras

why not try
sheet1.usedrange.clearcontents



bill gras wrote in message
...
I copy and paste a web page into excel in sheet1
The page I import changes every day and is different
in size , I delete all data in sheet 1 and copy and paste
the new web page in sheet 1.
I cannot use "clear contents " as the new web page size is
different to the last web page.
I have all my work sheet function formulas to extract data
from sheet 1 in sheet 2.When I delete sheet 1, I get #REF!
errors in all my formulas in sheet 2.
Is ther a way to over come this?
I would like a work sheet function if there is one

Thanks for all reply's

regards Bill
--
bill gras

Thanks for your reply
Can you tell me how and where to apply your formula
Thanks
--
bill gras


"R.VENKATARAMAN" wrote:

why not try
sheet1.usedrange.clearcontents



bill gras wrote in message
...
I copy and paste a web page into excel in sheet1
The page I import changes every day and is different
in size , I delete all data in sheet 1 and copy and paste
the new web page in sheet 1.
I cannot use "clear contents " as the new web page size is
different to the last web page.
I have all my work sheet function formulas to extract data
from sheet 1 in sheet 2.When I delete sheet 1, I get #REF!
errors in all my formulas in sheet 2.
Is ther a way to over come this?
I would like a work sheet function if there is one

Thanks for all reply's

regards Bill
--
bill gras

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...x2k.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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"bill gras" wrote in message ...
Thanks for your reply
Can you tell me how and where to apply your formula
Thanks
--
bill gras


"R.VENKATARAMAN" wrote:

why not try
sheet1.usedrange.clearcontents



bill gras wrote in message
...
I copy and paste a web page into excel in sheet1
The page I import changes every day and is different
in size , I delete all data in sheet 1 and copy and paste
the new web page in sheet 1.
I cannot use "clear contents " as the new web page size is
different to the last web page.
I have all my work sheet function formulas to extract data
from sheet 1 in sheet 2.When I delete sheet 1, I get #REF!
errors in all my formulas in sheet 2.
Is ther a way to over come this?
I would like a work sheet function if there is one

Thanks for all reply's

regards Bill
--
bill gras

actually should have just deleted this paragraph
since it should have just been "ALL" instead of "contents"
and had inserted similar information earlier.

The simplest would be to use the menus (corrected as follows)
Ctrl+A to select all cells (in Excel 2003 use instead Ctrl+Shift+Spacebar)
Edit, Clear, All

Thanks for your help , R.Venkataraman and David Mc Ritchie
very much appreciated
--
bill gras


"bill gras" wrote:

I copy and paste a web page into excel in sheet1
The page I import changes every day and is different
in size , I delete all data in sheet 1 and copy and paste
the new web page in sheet 1.
I cannot use "clear contents " as the new web page size is
different to the last web page.
I have all my work sheet function formulas to extract data
from sheet 1 in sheet 2.When I delete sheet 1, I get #REF!
errors in all my formulas in sheet 2.
Is ther a way to over come this?
I would like a work sheet function if there is one

Thanks for all reply's

regards Bill
--
bill gras