Assistant with VBA code from formula

[Newbie73]

Hello, yet another formula that I would like to transform into VBA code but improve the calculation times of a big excel spreadsheet.

In this one on Sheet3 pulls the information from Sheet1 and Sheet3 combining it in just one array to then be worked with other formulas.

test12.xlsx

Let me know if I didn't explain well or if you need any more information, thanks!

 

[Newbie73]

=LET(data,VSTACK(Sheet1:Sheet2!I2:L100000),FILTER(data,CHOOSECOLS(data,1)<>""))

Sorry should have added the formula itself here.

 

[RoryA]

How are you planning to use VBA? Functions written in VBA will generally not be faster than native Excel functions. That function would be better written as:

=LET(data,VSTACK(Sheet1!I2#,Sheet2!I2#),FILTER(data,CHOOSECOLS(data,1)<>""))

 

[Newbie73]

Hello Rory and thanks for the reply! Ah I see, I thought code would usually be more efficient.

I've tried your revised formula but gave me an #REF error, also the range is from I2 to L (the end number varies from spreadsheet to spreadsheet, that's why I added the 100000)

 

[RoryA]

It works as posted in your example file, so I guess you did something wrong.

 

[Newbie73]

I did indeed! Sorry about it, could you explain how/what does the # in I2# actually does? Didn't know about that one

 

[RoryA]

I2# means the entire array returned by the formula in I2

 

[Newbie73]

So that would be the whole array? Is there something similar for just a column (so until the columns no longer have results) and for a nomal range for example A1:B100000?

Because I do have this problem often, as data always change from sheet to sheet I usually put a safer number to make sure I cover cases where data is bigger than others

 

[RoryA]

If you just want a particular column you can wrap that in INDEX or CHOOSECOLS.

There isn't (yet, but it's coming!) an equivalent for normal ranges.