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.
 
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

This worked perfectly Many thanks for all your hep
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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