VBA code to expand selection based on value in next cell and then merge-and-center

[amarkham]

Hi,

I have a bunch of cells with either a value, eg "x" or nothing, eg "". I am having a lot of difficulty getting started on an algorithm that iterates through each row (starting A1, going to H1, then jumping to A2 and repeating the same thing), merging-and-center'ing based on the values in each cell.

Here's how it would work. Start in cell A1. There is an "x" in the cell. Cell B1 is blank (" "), add it to the selection so now it is A1:B1. Cell C1 is also blank - add it to the selection. Cell D1 has an "x" in it, now take A1:C1 (excluding D1), merge-and-center, apply outside borders, and do the same thing with D1. If it gets to H1 (the end column) without seeing another "x" (because the remaining cells are all empty) it merges-and-centers D1:H1 then proceeds to A2 and does the same thing.

Any ideas would be awesome. Thanks.

 

[amarkham]

So because the "FormulaRange" is only 6 rows and "Test" range is 78 rows, the above code correctly copies the formula across the first 6 rows of the "Test" range, not the rest of the 72 rows, whereas if I manually copy-paste-special (formula only) it will correctly propagate the formula across the entire selection.