CODEWITHSUNDEEP
vbaexcelmerge
mustelid
mustelid

Reputation: 23

Macro to merge cells in Excel for rows in which information in other columns matches

I am a programming novice. I taught myself HTML and CSS as a kid by looking at the source code on websites and futzing with it until I figured out why it worked, but I don't really have experience with any other type of coding. But, I learn best from being able to see code that does what I'm trying to do, and dissecting it (and explanations from kind and learned people always help).

I'm in a situation where I'm pretty sure I could use an Excel macro to do something, I just don't know how to accomplish it. (I think a macro is the right choice because it's not a one-off, it's something I'd have to apply on a regular basis. My issue is very similar to this one: Macro for merging cells

However, my goal is slightly more complicated. I want to do the same sort thing, only checking to see if there are multiple cells that are identical. So essentially, if multiple rows are identical in columns A, B, C, D, E, F, and O... then and only then, I want to merge the cells of those rows in column P.

I want to go from this: enter image description here

To this: enter image description here

Like I said, I know very little about VBA. I tried what seemed logical to me, which was taking the code in the answer from the linked post, but duplicating the "lastRow = [B2].End(xlDown).Row" bit to apply to each column I need to check. For what I'm sure are obvious reasons to someone who knows what they are doing, that did not work.

Any tips for how to edit this code to accomplish what I want to do, or a kind soul willing to write it so I can do some reading and backwards-engineer it? Please let me know if anything I've said is unclear and I really appreciate anyone who can help.

Upvotes: 2

Views: 67261

Answers (2)

Philip
Philip

Reputation: 11

Better Answer, please give me credit, this will merge cells to create a "Box" or whatever else you're looking to do with merging cells.

Hotkey is Control+M

Begin code:

Sub Mergethecells()
'
' Mergethecells Macro
' merge cells
'
' Keyboard Shortcut: Ctrl+m
'
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End Sub

Upvotes: 0

user2140173
user2140173

Reputation:

Should be quite easy...

Open you file and hit ALT+F11 to open the VBE. Right click anywhere in the VBA Project Window and insert a module (*standard module, not class, not userform )

copy paste the below code

Sub Main()

    Dim i As Long
    Dim j As Long

    Dim sameRows As Boolean
    sameRows = True

    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        For j = 1 To 7
            If StrComp(Cells(i, j), Cells(i + 1, j), vbTextCompare) Then
                sameRows = False
            End If
        Next j

        If sameRows Then
            Range(Cells(i, 8), Cells(i + 1, 8)).Merge
        End If

        sameRows = True
    Next i

End Sub

hit F5 to run the macro and that should merge the cells for you

remember the conditions are the rows have to be next to each other in a vertical sequence.

before running macro

enter image description here

and after

enter image description here

Upvotes: 8

Related Questions