Revert array formula using code


C

Cresta

Hello

I have a large worksheet with hundreds of array formula on it. Using VBA,
can anyone suggest how to go to each cell and remove the array part from each
formula. I tried replicating the keyboard way by using .Sendkeys "{F2}
{Return}" for each cell but it didnt work.

Any ideas
Thanks
 
Ad

Advertisements

M

Mike H

Hi,

I may be stating the obvious here but you do realise that if these are real
array formula as opposed to just array entered then they will no longer work.
Try this. Alt+f11 to open VB editor. Double click this workbook and paste
this in and run it

Sub Clear_Array()
For Each r In ActiveSheet.UsedRange
If r.HasArray Then
r.Formula = r.Formula
End If
Next
End Sub

Mike
 
M

Mike H

I did of course mean

Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and
paste the code in there and run it

Mike
 
Ad

Advertisements

C

Cresta

Thanks Mike that worked a treat.

Mike H said:
I did of course mean

Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and
paste the code in there and run it

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top