Create Dynamic Range Merging with Excel VBA

VBA Code: Create Dynamic Range Merging

Sub MergeDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim rng As Range, cell As Range
    Dim mergeStart As Range, mergeEnd As Range
    Dim currentValue As String
      ' Set the worksheet where the operation will be performed
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
    ' Find the last used row and column
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Loop through each column dynamically
    Dim col As Integer
    For col = 1 To lastCol
        Set rng = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)) ' Assume first row is header
        ' Initialize merging process
        Set mergeStart = rng.Cells(1)
        currentValue = mergeStart.Value
        ' Loop through each cell in the column
        For Each cell In rng
            If cell.Row = mergeStart.Row Then GoTo SkipIteration ' Skip the first row
            ' If the value is the same as previous, expand the merge range
            If cell.Value = currentValue Then
                Set mergeEnd = cell
            Else
                ' Merge the previous range if more than one row
                If mergeStart.Row <> mergeEnd.Row Then
                    ws.Range(mergeStart, mergeEnd).Merge
                    ws.Range(mergeStart, mergeEnd).HorizontalAlignment = xlCenter
                    ws.Range(mergeStart, mergeEnd).VerticalAlignment = xlCenter
                End If
                  ' Start a new merging range
                Set mergeStart = cell
                currentValue = cell.Value
            End If
SkipIteration:
        Next cell
          ' Final merge for the last group
        If mergeStart.Row <> mergeEnd.Row Then
            ws.Range(mergeStart, mergeEnd).Merge
            ws.Range(mergeStart, mergeEnd).HorizontalAlignment = xlCenter
            ws.Range(mergeStart, mergeEnd).VerticalAlignment = xlCenter
        End If
    Next col
    MsgBox "Merging completed successfully!", vbInformation, "Merge Complete"
End Sub

Detailed Explanation

1. Worksheet Selection
   • The script starts by defining the worksheet where the operation will be executed.
   • The Set ws = ThisWorkbook.Sheets(« Sheet1 ») line ensures that the script operates on the correct sheet.
2. Finding Last Used Row and Column
   • lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row identifies the last row with data in column A.
   • lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column determines the last used column.
3. Loop Through Columns
   • The script loops through each column dynamically using a For loop:

• For col = 1 To lastCol

1. • This ensures that the merging process works across all columns.
2. Initialize Variables for Merging
   • mergeStart is set to the first cell in the current column’s range.
   • currentValue stores the value of mergeStart to track consecutive duplicates.
3. Loop Through Each Cell
   • The For Each loop iterates through all rows in the column:

• For Each cell In rng

1. • If the cell value matches currentValue, mergeEnd is updated to include this cell in the merge range.
   • If the value changes, the script merges the previous group and starts a new merging sequence.
2. Merging Consecutive Duplicate Cells
   • The script merges only if the range contains more than one row:

• If mergeStart.Row <> mergeEnd.Row Then
• Range(mergeStart, mergeEnd).Merge
• Range(mergeStart, mergeEnd).HorizontalAlignment = xlCenter
• Range(mergeStart, mergeEnd).VerticalAlignment = xlCenter
• End If

1. • This ensures that isolated cells are not merged.
2. Final Merge for the Last Group
   • Since the loop might end before merging the last group, a final check ensures it merges any remaining range.
3. User Notification
   • At the end, a message box informs the user that the merging is complete.

Use Case

• This script is useful when you have tabular data with repeating values and want to merge them dynamically.
• It works for multiple columns without requiring manual selection.
• Ideal for structured reports or formatted tables.