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)

 

[Joe4]

Why merge them? Why not just delete them?

Often times, merged cells and VBA do not get a long, and most programmers will do everything possible to avoid using merged cells. I would recommend the same. See here: http://www.officearticles.com/excel/merged_cells_in_microsoft_excel.htm

 

[bobsan42]

what you're doing here is that you repeat rows.
you go to cell 1 - merge 1 till 5 for example - then go to cell2 but it actually selects cell 1 and try the same, then to row3 - again - it's kind of messy.
i would suggest a loop - something like:

i=1
Do while i > Rows.Count
  ..... some code .... f.e. merge 5 cells
 i = i + [I]number of merged cells[/I]
Loop

the main idea is to skip what you've already merged

 

[Joe4]

what you're doing here is that you repeat rows.
you go to cell 1 - merge 1 till 5 for example - then go to cell2 but it actually selects cell 1 and try the same, then to row3 - again - it's kind of messy.

Just like when deleting rows via VBA, it might make more sense to loop through the code backwards. Then you don't have that issue, i.e.

For i = Rows.Count to 1 Step -1
...
Next i

 

[bobsan42]

Just like when deleting rows via VBA, it might make more sense to loop through the code backwards. Then you don't have that issue, i.e.

Joe4,
i was thinking about this also at first glance, but i am pretty sure you are wrong here - with step -1 you still loop all the rows - no matter if they are already merged or not - why not skip the cells already merged?

 

[Joe4]

Joe4,
i was thinking about this also at first glance, but i am pretty sure you are wrong here - with step -1 you still loop all the rows - no matter if they are already merged or not - why not skip the cells already merged?

I never said you weren't looping through all the rows!
What I said was that would avoid the "mess" you described.

Here is some code that will work. I don't know how they are determining what the last row should be (if Column A is blank, I assume that they must be looking at some other column, like column B to determine where the last row of data is).

Sub MyMerge()
 
    Application.ScreenUpdating = False
    
    Dim i As Long
    Dim myLastRow As Long
    
'   Find last row (using entries in column B)
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through range backwards and merge
    For i = myLastRow - 1 To 1 Step -1
        If Cells(i + 1, 1) = "" Then Range(Cells(i, 1), Cells(i + 1, 1)).Merge
    Next i
    
'   Format entire column A
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
    Application.ScreenUpdating = True
    
End Sub

If there is no other data on the sheet (only column A), I go back to my original statment and say you should not use Merged Cells and simply delete the blank rows.

 

[baxtercavendish]

Thanks for the help guys, I actually went another way around it together, using the advice that coding w/ merged cells can get pretty tricky. I explained it to my manager and decided to keep cells in a repetitive format (not aesthetic, but easy).

 

[Bill C]

I found this which may come in handy. You select the cells in a column containing your data to merge and then it asks how many columns to the right you want the merge. Enter and it's done. Each row is merged to the cells to the right.

http://www.excelforum.com/excel-general/728392-merge-cells-in-each-row.html

Bill C

 

[cadetgideon]

I have a similar question and i have absolutly no experience with macros. I have a TV guide i am trying to format for printing so mergind cells is what i need to do. I need a macros to search through 7 tabs in my spreadsheet (a tab for each day of the week). It needs to search through multiple columns (each column is a channel). I need a macro that will go down the data and when a cell has tv show title in it, the macro merges all blank cells below the cell with the title in it. This will create a large block that will match the times i have in column A on the left when i show outside borders. here is an example:

Column A Column B Column C
Row1 12:00 Buffy UFC
Row2 1:00
Row3 2:00 NFL
Row4 3:00 WWE


So assuming Buffy was a 3 hour show, i would need the macro to merge B1 with B2 and B3 so that when i show borders that is one cell with the wording centered at the top of the cell. It would then merge C1 with C2 and then merge C3 with C4 and any other blank cells that might be below that until the next show is on. Any help on this is greatly appreciated.

 

[cadetgideon]

The post keeps changing the spacing on my example so i cant get it to format correctly and i cant attach a screen shot of my spreadsheet from what i can see. I just need the macro to merge all blank cells below a cell that has a tv show title in it. Then when it gets to a call with another tv show title in it, the macro merges all blank cells below that one. Column A has all the times in it so the macro can stop running when there is nothing else in column A. I would like it to run on all tabs in my workbook.