Help please - Graduation Project - Merge ALL cells with same value in a sheet. [rows AND columns]

[iJihad]

Hello there,

We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.

long story short, we ended up with an excel sheet like this:

Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)

I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.

But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.

I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.)

I googled for days, i only found Visual basic commands i guess? that only merge same rows. and they were poorly made. beside that it didn't work properly.

Could anyone please help me with a method to do it automatically?


EXCEL 2013


Thanks a lot!

 

[iJihad]

NOTE: i only posted the question on excelforum website here, i wouldn't do it normally, but we are late for deadline . sorry!


Also, what i'm looking for is merging all cells with same value if they are vertically or horizontally adjacent.

OR maybe merging all cells in a certain range if they are same value. Because in the whole sheet, no cells are with same value unless they are adjacent.

 

[GCExcel]

Like this :

Sub MergeCells()    Dim rg As Range, rgF As Range, c As Range
    Dim i As Integer
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set rg = Range("A1:Z30")        'DEFINE YOUR RANGE HERE
    
    On Error Resume Next
    For i = 1 To 50                 'VALUES YOUR ARE LOOKING FOR AND WANT TO MERGE
        For Each c In rg
            If c.Value = i Then
                If rgF Is Nothing Then
                    Set rgF = c
                Else
                    Set rgF = Union(rgF, c)
                End If
            End If
        Next c
        rgF.Merge
        Set rgF = Nothing
    Next i
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[iJihad]

Amazing!

Thanks ALOT, it worked perfectly for the sheet i'm working on.

Much thanks!