How to prevent deleting of cells messing funtion ranges

[Zetori]

Hiya

I have macro that brings information from outlook to excel. In the beginning of macro, it deletes range of cells. That destroys the functions that target those cells. Is there a way avoid that? Using some different method or ?

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim myCalItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
Dim ThisAppt As Outlook.AppointmentItem
Dim MyItem As Object
Dim StringToCheck As String
Set Mybook = Excel.Workbooks.Open("C:\Temp\name.xlsx")
   [B]Sheets("data").Select[/B]
[B]  Range("A1:G2000").Select[/B]
[B]  Selection.Delete Shift:=xlToLeft[/B]
Workbooks("name.xlsx").Close SaveChanges:=True

Running the macro messes up all funtions that targets those cells.

Like:

=DATEVALUE(MID(data![B]#REF[/B]!;4;2)&"."&LEFT(data![B]#REF[/B]!;2)&"."&RIGHT(data![B]#REF[/B]!;2))

PS. This really great code to get data from outlook is orginally: http://www.codeforexcelandoutlook.com/blog/2008/08/extract-calendar-data-from-outlook-into-excel/

Thank you!

 

[Andrew Poulsom]

Can't you just use ClearContents instead of Delete?

 

[Zetori]

Can't you just use ClearContents instead of Delete?

Ofcourse! I feel stupid now :D Thank you for guiding me to right direction. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

Changed code to:

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim myCalItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
Dim ThisAppt As Outlook.AppointmentItem<o:p></o:p>
Dim MyItem As Object<o:p></o:p>
Dim StringToCheck As String<o:p></o:p>
Set Mybook = Excel.Workbooks.Open("C:\Temp\name.xlsx")
Worksheets("data").Range("A1:G2000").ClearContents
Workbooks("name.xlsx").Close SaveChanges:=True

<o></o>
<o></o>
<o>Thank you!</o>