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.

 

[Fluff]

Hi & welcome to MrExcel
Are you "blank" cells actually blank, ie completely empty?

 

[amarkham]

No, every cell is a formula which either outputs some text (in my scenario above, "x") or "", so that if I try and merge-and-center manually I get a "merging cells only keeps the upper-left value and discards other values" message.

 

[Fluff]

If you merge cells will only keep the value of the topleft cell, all other values/formulae will be lost.
Is that what you want?

 

[amarkham]

Yes, that is fine. At the beginning of the macro I will un-merge the whole thing, copy and paste the formula, and then run the merge-and-center algorithm.

 

[Fluff]

Merge cells are IMO an abomination & should be avoided like the plague, because they create havoc.
Would you be interested in "Centre across selection" instead, which will look the same, but without the problems

 

[amarkham]

Ya that might work better actually! Only thing is that it would have to delete all the "" blank cells otherwise center-across-selection just centers text within its cell if you follow.

 

[Fluff]

How about

Sub CentreSelection()
   Dim lr As Long, i As Long
   Dim Rng As Range
   lr = Range("A:H").find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
   With Range("A1:H" & lr)
      .Value = .Value
   End With
   For i = 1 To lr
      On Error Resume Next
      For Each Rng In Range("B" & i).Resize(, 7).SpecialCells(xlBlanks).Areas
         Rng.Offset(, -1).Resize(, Rng.count + 1).HorizontalAlignment = xlCenterAcrossSelection
      Next Rng
      On Error GoTo 0
   Next i
End Sub

This will turn all your formulae in values if that's ok.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...e-in-next-cell-and-then-merge-and-center.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[amarkham]

Sorry about the cross posting - I was not aware of the rules and not sure how to delete my post.

Thank you!! Your code works great but I have a couple follow-up questions: how do I make the macro work within a specified range only (eg A4:M84)? I am hoping to add to the macro to copy-paste a formula over all of the cells in that range before running your code. Also, after centering across selection how can I add an outside border as well?

Btw I live in Canada but was just in Chippenham last week visiting family. Lovely part of the countryside.