Revert array formula using code

[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

 

[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

 

[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

 

[Cresta]

Thanks Mike that worked a treat.

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