Merge cells macro - loop through column

B

baxtercavendish

New Member
Joined
Aug 27, 2009
Messages
17
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:
Code: 
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)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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:
Code: 
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
 
Upvote 0
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.
Click to expand...
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.

Code: 
For i = Rows.Count to 1 Step -1
...
Next i
 
Upvote 0
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.
Click to expand...
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?
 
Upvote 0
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?
Click to expand...
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).
Code: 
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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Applying VBA to other cells
Replies
2
Views
159
NoSparks
N
S
  • Question
Mail Merge Macro Doesn't Connect Refresh
Replies
1
Views
44
StephenBart
S
C
  • Solved
Merge based on Merged area of adjacent columns
Replies
2
Views
582
CodeNewb
C
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
262
Joe4
Joe4
J
  • Question
Shifting multiple cells left/right and ignoring certain columns?
Replies
1
Views
74
joshpep96
J

Forum statistics

Threads
1,214,621
Messages
6,120,568
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top