Macro to merge cells

dment

New Member
Joined
Dec 4, 2008
Messages
16
Hi

I a trying to create a macro that will merge a number of cells in a column based on a value in another cell

the spreadsheet is laid out

Column A Column B Column C Column D Column E

City ProductID Cost Total Amount Count
London 001 £1.00 £7.00 5
London 002 £2.00
London 008 £8.00
London 004 £4.00
London 002 £2.00
Birmingham 003 £3.00 £20.00 5
Birmingham 005 £5.00
Birmingham 004 £4.00
Birmingham 005 £5.00
Birmingham 003 £3.00
Manchester 001 £1.00 £16.00 6
Manchester 002 £2.00
Manchester 004 £4.00
Manchester 001 £1.00
Manchester 005 £5.00
Manchester 003 £3.00

I need to merge column D for each city.
I had a macro that used xlDown but this takes it down to the bottom of the workbook rather than the next cell which has a figure in (This is due to the spreadsheet having a formula in the cells) so I think I will need to use the count figure in column E

Any help will be much appreciated.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If you need the sum of the Cost, try this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;background-color: #92D050;;">City</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">ProductID</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Cost</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Total</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Amount</td><td style="font-weight: bold;text-align: center;background-color: #92D050;;">Count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">London</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">17</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">London</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">London</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">London</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;;">London</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">Birmingham</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">20</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;;">Birmingham</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;;">Birmingham</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;;">Birmingham</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;;">Birmingham</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;;">Manchester</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;;">Manchester</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="color: #333333;;">Manchester</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="color: #333333;;">Manchester</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="color: #333333;;">Manchester</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="color: #333333;;">Manchester</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)"></p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">$A$2:A2,A2</font>)=1,SUMIF(<font color="Red">$A$2:$A$17,A2,$C$2:$C$17</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

dment

New Member
Joined
Dec 4, 2008
Messages
16
Thanks but it's not the sum that I want. The example didn't come out as it should have.

Based on your pic column D should be Total Amount and column E is the count.

I need Macro that will merge column D based on the count n column E

so Cells D2 to D6 will be merged. D7 to D11 will be merged and so on
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Based on my pic, cell D2 has value, cells D3 to D6 are empty, and so on.
If the above is correct, then try the following:

Code:
Sub merge_cells()
  Dim c As Range
  For Each c In Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Areas
    With c.Offset(-1).Resize(c.Rows.Count + 1)
      .MergeCells = True
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
    End With
  Next
End Sub
 

dment

New Member
Joined
Dec 4, 2008
Messages
16

ADVERTISEMENT

Thank you. That works in my test spreadsheet but not on the one I need it to work on. problem is the spreadsheet is created from one that has formulas in column D so while the cells are blank End + Down takes you to the end of the column rather than the next number. If I click in each cell in column D before running the macro it works fine. This is why I included column E which holds the number of cells to be merged.

Any ideas of how best to get round this problem?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
If col D had formulae that returned "", try
Code:
Sub merge_cells()
    Dim c As Range
    With Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
        .Value = .Value
        For Each c In .SpecialCells(xlCellTypeBlanks).Areas
            With c.Offset(-1).Resize(c.Rows.Count + 1)
                .MergeCells = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
            End With
        Next c
    End With
End Sub
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

In column E do you also have formulas?

Do you want to keep the formula in column D, in this case on the merged cell?

Try this:

Code:
Sub merge_cells2()
  Dim ant As Variant, i As Long, ini As Long
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  ini = 2
  ant = Range("A2").Value
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
    If ant <> Cells(i, "A").Value Then
      With Range("D" & ini & ":D" & i - 1)
        .MergeCells = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
      End With
      ini = i
    End If
    ant = Cells(i, "A").Value
  Next
End Sub
 

dment

New Member
Joined
Dec 4, 2008
Messages
16
Thanks. That is almost what I am after. the cells merge OK apart from the last one. the original formula goes down to row 25 so the amount for Manchester merges D12 to D25 instead of D12 to D17
 

dment

New Member
Joined
Dec 4, 2008
Messages
16
Thanks. That is almost what I am after. the cells merge OK apart from the last one. the original formula goes down to row 25 so the amount for Manchester merges D12 to D25 instead of D12 to D17

This was in reply to the macro from Fluff
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I don't know how you have the formulas.
You could put how you have the formulas.
Or change the references of the formulas to absolute, that is, if you have this: D12:D17 you must change it to: $D$12:$D$17
 

Watch MrExcel Video

Forum statistics

Threads
1,113,935
Messages
5,545,101
Members
410,656
Latest member
Hydraulics
Top