Merging/deleting duplicate cell values

Question

I have an issue with merging cells with same values. I receive excel files that vary in columns and rows amount so I can't use macros set for stiff amount of rows or columns. What I have is like this

The first row is of course header row that have filter in them. What I want to have should be like this

So I can still sort things by all columns (so cool bikes will still show me 4 rows) Is there a macro or a script that can do that and not get not responsive from the size of sheet (few thousand rows sometimes) AND will not need to be manually change and set up for every sheet?

Thanks in advance for any help.

Answer 1

If this is a display issue, you can merely use Conditional Formatting.

The Rule would be:

=$C1=$C2

and the Format would be under Number Format the custom format of: ;;;

And, no matter by what column you sorted, the tagline would also apply to the blank appearing rows above it, just as in your example.

This sorting and application of Conditional Formatting could be done in a macro, if you like.

However, you would have to sort by tagline last in order to keep all bikes with the same tagline grouped together.

Answer 2

This Macro should work:

Public Sub merger()
    Application.DisplayAlerts = False
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Sheet1")
    firstrow = 2
    lastcolumn = "Z"
    columnsortkey = "C"
    columnmerge = 3
    Columns("A:" & lastcolumn).Sort key1:=Range(columnsortkey & ":" & columnsortkey), Header:=xlYes
    theEnd = False
    thisrow = firstrow
    found = False
    Count = 0
    While theEnd = False
        firstCell = wks.Cells(thisrow, columnmerge)
        nextCell = wks.Cells(thisrow + 1, columnmerge)
        If firstCell = nextCell Then
            If found = False Then
                found = True
                mergerfirst = thisrow
                Count = Count + 1
            Else
                Count = Count + 1
            End If
        Else
            If found = True Then
                Range(Cells(mergerfirst, columnmerge), Cells(mergerfirst + Count, columnmerge)).Merge
                found = False
                Count = 0
            End If
        End If
        thisrow = thisrow + 1
        If firstCell = "" Then theEnd = True
    Wend
    Application.DisplayAlerts = True
End Sub

To make it work you have to open VBA/Macros with ALT+ F11, then under ThisWorkbook insert a new module and paste the code.