Merge duplicated cells in bulk per each column VBA Need to merge data per column if records in the first column are the same.

I wrote a VBA code and it works but it works very slow as it executes "merge" function per each row no matter if consequent cells are the same or different.

Sub DuplicateValues()

'Declare All Variables:

Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
Dim k As Integer

Dim myFirstRow As Long
Dim myFirstColumn As Long

Dim rngCopy As Range, rngPaste As Range

'Count Number of Rows and Columns:

Application.DisplayAlerts = False

myRow = Range(Cells(3, 1), Cells(3, 1).End(xlDown)).Count + 2 'first two rows are headers
myCol = Range(Cells(2, 1), Cells(2, 1).End(xlToRight)).Count

'Apply formatting to the end of the table:

With ActiveSheet
Set rngCopy = .Range(.Range("A3"), .Cells(myRow, myCol))

 With rngCopy.Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .Weight = xlThin
    End With
End With

'Merge duplicated cells per Row:

myFirstRow = 3
myFirstColumn = 1

Set iRow = Cells(myFirstRow, myFirstColumn)
Set n = Cells(myFirstRow + 1, myFirstColumn)

For i = 1 To myRow

    If iRow &lt;&gt; n Then

                Range(Cells(myFirstRow, 1), Cells(myFirstRow, myCol - 3)).HorizontalAlignment = xlCenter
                Range(Cells(myFirstRow, 1), Cells(myFirstRow, myCol - 3)).VerticalAlignment = xlCenter
                Range(Cells(myFirstRow, 8), Cells(myFirstRow, 8)).WrapText = True
                Range(Cells(myFirstRow, 1), Cells(myFirstRow, myCol - 3)).EntireRow.AutoFit       

                iRow = Cells(myFirstRow + i, myFirstColumn)
                n = Cells(myFirstRow + i + 1, myFirstColumn)

                Else
                n = Cells(myFirstRow + i + 1, myFirstColumn)

                    For k = 1 To myCol - 3 'need to merge data per column but don't need to merge data in the last 3 columns

                        Range(Cells(myFirstRow + i - 1, k), Cells(myFirstRow + i, k)).Merge
                        Range(Cells(myFirstRow + i - 1, k), Cells(myFirstRow + i, k)).HorizontalAlignment = xlCenter
                        Range(Cells(myFirstRow + i - 1, k), Cells(myFirstRow + i, k)).VerticalAlignment = xlCenter
                        Range(Cells(myFirstRow + i - 1, 8), Cells(myFirstRow + i, 8)).WrapText = True
                        Range(Cells(myFirstRow + i - 1, k), Cells(myFirstRow + i, k)).EntireRow.AutoFit

                    Next

    End If
Next

End Sub


I  tried to create a different logic to initiate "merge" by blocks (skip merging the same value cells in the first column right away):
merging only when the "base/initial" cell is not the same as a "check" cell and merge all appropriate cells all together (not by each row), but I can't find a way to make it work.

Would be very grateful for any code optimizations!
'Declare All Variables:

Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
Dim k As Integer

Dim myFirstRow As Long
Dim myFirstColumn As Long

Dim rngCopy As Range, rngPaste As Range



Don't do this. This procedure is somewhere between 2 and 3 screens high on my laptop: when I'm at the bottom of the procedure, I don't know what I'm looking at, so I scroll back up to this wall of declarations, parse the chunk of code, locate the variable I'm looking for, then scroll back down to where I was, ...and that gets very annoying, very fast.

People defending this "declare everything at the top of the procedure" habit usually defend it pretty hard, and they're wrong. "I can see everything that's used in the procedure at once" sounds great on paper. Truth is, it only makes you scroll back and forth all the time for no reason, and makes it much harder than it should be, to know whether or not a variable is used, let alone where it's used.

If you declare variables where you first assign them, as you need to introduce them, then you simply can't miss the fact that myCell, myRange, rngPaste are never assigned or referenced anywhere... and that iRow and n aren't declared at all. This is extremely bug-prone, you don't want to allow VBA code to run with undeclared variables. Fortunately you can completely prevent this, by simply specifying Option Explicit at the very top of every module you ever write any VBA code in.

Proper indentation also helps, massively. It started well:

For i = 1 To myRow

    If iRow &lt;&gt; n Then


...and then suddenly went way out there:

                Range(Cells(myFirstRow, 1), Cells(myFirstRow, myCol - 3)).HorizontalAlignment = xlCenter


...to seemingly random:

            Else
            n = Cells(myFirstRow + i + 1, myFirstColumn)

                For k = 1 To myCol - 3 'need to merge data per column but don't need to merge data in the last 3 columns


Use an indenter if you're unsure how to keep indentation consistent.

Row numbers should always be As Long. An Integer is a 16-bit signed integer type, so its maximum possible value is 32,767. This means as soon as your data involves row 32,768 and beyond, your code breaks with an "Overflow" run-time error. In fact, there's little to no reason at all to ever declare anything As Integer in 2019: processors are optimized to deal with 32-bit integer types, and that's a Long in VBA.

I like this:


With ActiveSheet



I makes ActiveSheet references explicit, and that's very good.

However you're implicitly referring to whatever the ActiveSheet is everywhere else outside this With block.


myRow = Range(Cells(3, 1), Cells(3, 1).End(xlDown)).Count + 2 'first two rows are headers
myCol = Range(Cells(2, 1), Cells(2, 1).End(xlToRight)).Count



Unqualified like this, Range and Cells are implicitly referring to the ActiveSheet: the With ActiveSheet block is therefore redundant, and makes accesses to that active sheet inconsistent: some are explicit, some are implicit - and that's very bug-prone.

A note about this:

myRow = Range(Cells(3, 1), Cells(3, 1).End(xlDown)).Count + 2 'first two rows are headers


Looks like you're trying to get the last used row; this is not a reliable way to do it. Consider flipping the logic around and going up from the last row on the sheet to get the .Row of that specific cell, rather than the Count of cells in a range:

myRow = ws.Range("A" &amp; ws.Rows.Count).End(xlUp).Row


Note how the fist two rows being headers, doesn't matter any more.

I'd strongly advise against using my as a prefix for anything, regardless of how often you see it in documentation. Same goes for rng and o and whatever other prefixing scheme you might read anywhere. Use meaningful names instead. "myRow" doesn't say what's important about it: it's the last row with data. lastRow would already be a much better name for it.

rngCopy is assigned, its borders are formatted, ...but it's not being copied anywhere. The name strongly suggests it's a source, especially since it's declared right next to the unused rngPaste, which strongly suggests that would be a destination for an operation involving the clipboard. Pretty misleading, since none of that is happening anywhere in the procedure.

I already mentioned n isn't declared. Its usage is a problem.

Set n = Cells(myFirstRow + 1, myFirstColumn)


It's Set-assigned to a Range. Thus, its runtime type at that point is Variant/Range.

If iRow &lt;&gt; n Then


Now it's being compared to iRow, which is another undeclared Variant/Range variable, and the two objects are only comparable through implicit let-coercion involving hidden default member calls - making it all explicit would look like this:

If iRow.Value &lt;&gt; n.Value Then


And while that's now explicit, it's wrong. It's wrong, because they shouldn't be Range objects. They shouldn't be Range objects, because they're later being Let-assigned to plain values:

        iRow = Cells(myFirstRow + i, myFirstColumn)
        n = Cells(myFirstRow + i + 1, myFirstColumn)


Beyond that point, the data type of iRow and n is up in the air. If you're lucky, you're looking at two Variant/Double values. Otherwise, you're looking at one or more Variant/Error values (that would be the case if the worksheet contains error values), and everything blows up with a type mismatch error.

Not declaring variables is one thing, reusing variables is another, but reusing undeclared variables and assigning them a new runtime type, makes everything very hard to follow. Declare them with an explicit data type, and let them be values, instead of objects that behave like values thanks to implicit hidden code.

As for performance, the reason it's slow is because it's doing the single slowest thing any code can do: interact with worksheet cells directly, in a loop.

You can't help it, interacting with worksheet cells is precisely what this macro needs to do. All you can do is tell Excel to stop trying to keep up as you modify the worksheet.

Every time a cell is modified, Excel evaluates whether a recalc is needed, and will perform it if it has to. Switch calculation to manual before you start manipulating the worksheet, and toggle it back to automatic when you're done: Excel will only recalculate once. That's Application.Calculation (set to xlCalculationManual, then back to xlCalculationAutomatic when you're ready).

Every time a cell is modified, Excel fires worksheet events (e.g. Worksheet.Change), and if there's a handler procedure for that event, then that code will run before the next instruction does. Turn off application events before you start manipulating the worksheet, and toggle it back on when you're done: no worksheet events will be fired; that's Application.EnableEvents (set to False, then back to True when you're ready).

Every time a cell is modified, Excel tries to repaint itself. While that's normally pretty fast, when you're modifying cells in a loop you don't want Excel to even try to keep up - you want to make your changes, and have Excel repaint itself when you're done. Do that by toggling Application.ScreenUpdating (set to False, then back to True when you're ready).

Whenever you toggle this global Application state, make sure you handle runtime errors, and have your error-handling subroutine ensure that the global state gets properly reset whether the procedure succeeds or fails, whatever the reason for failing might be.