CODEWITHSUNDEEP
excelvba
Justin Dearing
Justin Dearing

Reputation: 14928

Clearing a dynamic range that might be empty

If I define a dynamic named range Foo like =OFFSET(sheet!$A$2,0,0,COUNTA(sheet!$A$2:$A$1048576 ),1), I can clear it like so from VBA:

Me.Range("Foo").Clear

However, that will fail if the range is already clear. I can work around it with

On Error Resume Next 'If this reange is clear it errors out.
Me.Range("Foo").Clear
On Error GoTo 0

I'd like a cleaner way to see if a named range exists, and is invalid though.

Upvotes: 0

Views: 703

Answers (2)

John Coleman
John Coleman

Reputation: 51998

You can use Evaluate like thus:

Sub test()
  If TypeName(Evaluate("Foo")) = "Range" Then Evaluate("Foo").Clear
End Sub

If you run it twice in a row with your sample dynamic range, the first time it clears the data and the second time it gracefully does nothing.

Upvotes: 2

user4039065
user4039065

Reputation:

The reason that you cannot clear Foo is that Foo is in an error state; e.g. it doesn't exist. Your formula defines Foo as having no cells if it has already been cleared and while a named range can exist in an error state, you cannot reference it until it has at least one value even if that value is a zero-length string returned by a formula. In short, the COUNTA function is returning zero so Foo is zero rows high by one column wide.

You could make sure that Foo is at least one row high even if that cell is blank.

=OFFSET(sheet!$A$2, 0, 0, MAX(1, COUNTA(sheet!$A$2:$A$1048576 )), 1)

If you plan to let Foo occasionally exist in an error state and use On Error Resume Next to handle trying to clear nothing then this would be a better formula.

=Sheet!$A$2:INDEX(sheet!$A$2:$A$1048576, MATCH("zzz", $A$2:$A$1048576))

That assumes that there is text in column A (you used COUNTA, not COUNT). If there are numbers then,

=Sheet!$A$2:INDEX(sheet!$A$2:$A$1048576, MATCH(1e99, $A$2:$A$1048576))

Upvotes: 1

Related Questions