i'm using this formula to stack non-contiguous column ranges into contiguous ranges, where the named ranges are:
BR_StudentID represents non-contiguous range with Blanks in Z2:Z
NBR_StudentID represents intended contiguous No Blanks range in A2:A
formula in A2:A:
The problem is Excel takes up quote a long time to calculate the whole sheet with this formula in place, everytime data is updated in Z2:Z, it spins for quite awhile, that i can read the news on my phone. I did some tests to compare times taken with slightly more rows and what i found was the time required to process the sheet using this formula exponentially increases disproportionately with the few additional number of new rows with data. Indeed, the formula is simply not efficient, and i'm wondering if there is a macro code that can accomplish the same task but faster? I'm familiar with formulas but not so much with vba.
Will appreciate any help that works