How to merge cells based on similar values - Excel 2010

Question

I have a problem merging cells in excel based on similar values for one column- I would like to keep other columns data - let's view some screenshots and it will be clearer:

This above is the initial state of the Data, what I want to achieve is this:

I'm sure there is a way to do it with VB or formulas- I need the most simple way possible as this is for a customer and it needs to be easy.

Thank you all in advanced.

Solution

Option Explicit

Private Sub MergeCells()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim rngMerge As Range, cell As Range
    Set rngMerge = Range("A1:A100") 'Set the range limits here

MergeAgain:
    For Each cell In rngMerge
        If cell.Value = cell.Offset(1, 0).Value And IsEmpty(cell) = False Then
            Range(cell, cell.Offset(1, 0)).Merge
            GoTo MergeAgain
        End If
    Next

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

You can hardcode the range limits (i.e. the last row in Column A to check), have the user input it each time, or programmatically find the last row in the range . Either way, this should get you started.

By the way, you could find the last row in column A with the following:

Dim i As Integer
    i = Range("A1").End(xlDown).Row
    Set rngMerge = Range("A1:A" & i)

Comments

[Luc]

What is the best way to extend this macro to run over all columns?