Clear Contents in Range is outside the range

[YellowLabPro]

My program is to clear out data from A2:I.
If there is data in A2 then the program runs as intended.
If there is no data in A2 and the program runs, it clears the data in row 1.

1)I placed a line of code that looks to see if there is no data in A2, if there is no data it is supposed to put a term in A2, "Clear". The first issue is that the term does not appear but rather a "0" which is a curiousity. And Secondly it still clears all the data beginning in A2 and then row 1.

I could put insert an Array of the values to replace the ones that are cleared if this were to happen I suppose, but I would rather discover why this is happening and hopefully fix the issue, not treat the sypmtom. I will need someone's help though...

[VBA]
Option Explicit
Sub Clear()
Dim ws As Worksheet
Dim LRow As Long
Set ws = Workbooks("TGSImporter.xls").Sheets("Update")
'Variable
LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.Range("A2").Formula = "=IF(A2="""",""Clear"",A2)"
ws.Range("A2:I" & LRow).ClearContents
'ws.Range("A2:I" & LRow).ClearFormats
[A2].Activate
End Sub
[/VBA]


Help...

Ylp

[mdmackillop]

This is creating a circular reference.
[VBA]ws.Range("A2").Formula = "=IF(A2="""",""Clear"",A2)"
[/VBA]
This clears the range which includes the cell you've just written your formula into
[VBA]
ws.Range("A2:I" & LRow).ClearContents
[/VBA]

[RichardSchollar]

Hi Ylp

1)I placed a line of code that looks to see if there is no data in A2, if there is no data it is supposed to put a term in A2, "Clear". The first issue is that the term does not appear but rather a "0" which is a curiousity. And Secondly it still clears all the data beginning in A2 and then row 1.

What your code is actually doing is inserting a formula into cell A2 which refers to itself (ie to cell A2) thus creating a circular reference. This happens regardless of whether there actually was any data in A2 to begin with. This is bad because:

i) You are effectively intending to check the value of your variable LRow by inserting a formula into your worksheet. This is both unnecessary and slow - you can do the checking in code and perform any actions as a result without resorting to modifying the original sheet.

ii) You will only get a return value of 0 because you have introduced a circular reference.

Your line of code here:

ws.Range("A2:I" & LRow).ClearContents

always starts at A2 and deletes everything to row LRow - where LRow is row 1 therefore, this will delete the formula you have just inserted and thus your hoped for string "Clear".

To modify (and assuming I have understood your requirements properly) you can use:

Option Explicit Sub Clear() 
Dim ws As Worksheet 
Dim LRow As Long 
Set ws = Workbooks("TGSImporter.xls").Sheets("Update") 
'Variable 
LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row 
If LRow = 1 Then 
   ws.Range("A2") = "Clear"
   ws.Range("A1:I1").ClearContents
Else
   ws.Range("A2:I" & LRow).ClearContents 
End If
'ws.Range("A2:I" & LRow).ClearFormats 
[A2].Activate 
End Sub

Hope this makes sense!

Richard

[YellowLabPro]

Thanks Richard,
That helps a great deal. Your code however still deletes the contents in row 1, which is what I am trying to prevent.
The term "Clear" was just term I used to insert temporarily to prevent the clearing of row 1.

I attempted to change your code to stop at line 2, but was not successful. I am still not able to achieve the desired results.

[YellowLabPro]

Here is how I have resolved it.
[VBA]
Dim ws As Worksheet
Dim LRow As Long
Set ws = Workbooks("TGSImporter.xls").Sheets("Update")
'Variable
LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
If LRow = 1 Then
' ws.Range("A2") = "Clear"
ws.Range("A2:I2" & LRow).ClearContents
Else
ws.Range("A2:I" & LRow).ClearContents
End If
'ws.Range("A2:I" & LRow).ClearFormats

[A2].Activate
End Sub
[/VBA]

Richard, your explanation helped tremendously, helping me understand exactly what was happening.
This may not be the ideal way to handle this, but for now it is an interesting way to deal w/ it.

thanks,

YLP

[johnske]

Hi YLP, what you want to do should be a simple 'one-liner' and what Malcolm gave you will still do this with the inclusion of an If statement to exclude row one from being cleared if there's no later entries. i.e. [VBA]If LRow > 1 Then ws.Range("A2:I" & LRow).ClearContents[/VBA]

[YellowLabPro]

Great, thanks John