asked on

vba - merging duplicate cells

Hello,

I need some assistance on merging several groups of duplicate cells in a column using a VBA macro.

So in the example below (or see attached), I need to merge all the adjacent duplicates e.g. AAA's into one cell, same with the BBB, CCCs, and DDDs.

Any code samples or insights to get me started in the right direction are greatly appreciated

Column 1

AAA
AAA
AAA
AAA
AAA
BBB
BBB
BBB
BBB
BBB
BBB
CCC
CCC
CCC
CCC
DDD
DDD
DDD
DDD
SAMPLE.xlsx

Alok-Agarwal

Please refer the link below
http://www.mvps.org/dmcritchie/excel/merge.htm

ASKER CERTIFIED SOLUTION

Alok-Agarwal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcha

ASKER

Alok, that worked perfect..thanks!

Sorry to ask an add on question but is there a easy way to center (both horizontally and vertically) the value in each merged cell? I'm not sure how to do it with the code here.

In the past, I've done something like below.... i can create another question if this is a lot of additional work for u.

range("A1").Select
 
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

Open in new window

Alok-Agarwal

Hi, Would really appreciate if you can please create another question. I desperately need some points. Sorry about that.

lcha

ASKER

you got it.

see question - https://www.experts-exchange.com/questions/26391049/center-results-of-this-macro.html

Alok-Agarwal

Hi, I have posted the answer. Kindly acceept the answer on this one if you are happy with the solution.