Delete Formula Array through VBA

[brage98]

We have an excel sheet with 4 or 5 different FormulaArrays.

Now we have to (why? long story, we just have to) remove this formulaArray (I need to repopulate the same region with some new data)

The process of deleting the formulaArray needs to be automated through vba code (why? again, long story!)

The question is how can I delete a formula array through VBA?

I've started off with something like this, but it's not quite there yet:

Dim rng As Range
Set rng = wsIndexData.Range(wsIndexData.Cells(iStartRowIndex, iStartColIndex), wsIndexData.Cells(iStartRowIndex + 10000, iStartColIndex + 4))
rng.CurrentArray.Delete

Thanks!

 

[SkipVought]

Hi,

Why not start with the FORMULAS from your "4 or 5 different FormulaArrays" and use the Find method?

Loop thru each of the formulas to find the range and replace with whatever.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

or why wouldn't this work?

'...
rng.ClearContents

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[brage98]

hey,
thanks for the reply

rng.Clearncontents doesn't work because I get the "You cannot change part of an array" error

as for your first suggestion, do you mind posting a simple code-example please

thanks!

 

[brage98]

k, thanks for your help... Turns out I was off in my range selection (off by 1). Once I found the correct range, I tried the following and it works:

wsIndexData.Range(wsIndexData.Cells(2, 1), wsIndexData.Cells(500, 5)).FormulaArray = Null

cheers!

 

[SkipVought]

I may have understood you.

What is a 'FormulaArrays?'

I know what an array formula is.

Please post one of the formulaarrays that you refer to.

Please state the range in which this entity resides.

Please describe the cells that are contrguous to this range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

To search whole worksheet and clear arrays:

For Each Cell In wsIndexData.UsedRange
    If Cell.HasArray Then Cell.CurrentArray.ClearContents
Next Cell

combo