Merge cells macro - loop through column

[baxtercavendish]

Trying to write a basic macro here that will go through a column searching for blank cells, and merge them with the first activecell.end(xlUP). Here is an example of the data:

Americas
(blank)
(blank)
Americas
(blank)
(blank)
(blank)
(blank)
Americas
Americas
Americas
(blank)
(blank)

Here is my current code, again, very basic:

Sub merge()


For i = 1 To Rows.Count
If Cells(i + 1, 1) = "" Then
Cells(i, 1).Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(ActiveCell.Address, ActiveCell.End(xlUp)).Select
   
   With Selection
   .merge
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlCenter
   End With

End If
Next i

End Sub

Current issue is it will merge the first "Americas" with the following blanks, then the next "Americas" and blanks, but then it Selects both "Americas" that have been merged and presents this msgbox "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." Is there a way to get it to not select the multiple data values and move on to the next line to search and merge?

Results in:
Americas (merged 3 cells)
Americas (merged 5 cells)
both of which are now selected so that if you press Ok in the msgbox it turns into "Americas" (8 cells merged) and executes the next merge that has blank spaces (repeats same issue)

 

[cadetgideon]

bump

 

[Joe4]

cadetgideon's question re-posted here: http://www.mrexcel.com/forum/showthread.php?t=599425

Please post any replies there.

 

[robert_gabriel]

Joe4's code is great.

My only problem is it only will merger what is in column "A",
I changed it to "B:B" but it still merge's "A".
a simple cut and paste of column "B" into "A" is an easy workaround but I wonder with the code change didn't get it.

Sub MyMerge()
....
End Sub

 

[Joe4]

My only problem is it only will merger what is in column "A",
I changed it to "B:B" but it still merge's "A".
a simple cut and paste of column "B" into "A" is an easy workaround but I wonder with the code change didn't get it.

That just changes the formatting part of the macro, you also need to address the loop part.

Note that format of Cells(row,column)
So, references like Cells(i,1) refer to column "A" (1="A").
If you wanted to apply it to column B, you would need to change it to Cells(i,2) as 2="B".
So you need to update those three Cells(..) references in the looping part of the code.

Alternatively, you could also write Cells(i,2) as Cells(i,"B"), if that makes it easier to see what is going on.