What is the smartest way for this scenario?

[prabha_friend]

[TABLE="width: 1567"]
<tbody>[TR]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]AU
[/TD]
[TD]BD
[/TD]
[TD]BD
[/TD]
[TD]BD
[/TD]
[TD]BD
[/TD]
[TD]BD
[/TD]
[TD]BD
[/TD]
[TD]BD
[/TD]
[/TR]
[TR]
[TD]Agents
[/TD]
[TD]Agents
[/TD]
[TD]Cash Application
[/TD]
[TD]Cash Application
[/TD]
[TD]Collections
[/TD]
[TD]Collections
[/TD]
[TD]Credit Application
[/TD]
[TD]Credit Application
[/TD]
[TD]Customer Master
[/TD]
[TD]Customer Master
[/TD]
[TD]Agents
[/TD]
[TD]Agents
[/TD]
[TD]Cash Application
[/TD]
[TD]Cash Application
[/TD]
[TD]Collections
[/TD]
[TD]Collections
[/TD]
[TD]Credit Application
[/TD]
[/TR]
[TR]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[/TR]
</tbody>[/TABLE]
Has to Merge and Center the recurring cells Dynamically...

 

[hippiehacker]

and what for criteria row 2? row3? row1?

 

[prabha_friend]

Except row3

 

[prabha_friend]

Sub Mac4()
Do Until ActiveCell <> ActiveCell.Next
    Union(Selection, ActiveCell.Next).Select
    Selection.Cells(Selection.Cells.Count).Activate
    If Selection.Cells(Selection.Cells.Count) <> ActiveCell.Next Then
        DisplayAlerts = False
        Selection.Merge
        DisplayAlerts = True
        Selection.Cells(Selection.Cells.Count).Next.Activate 'Union brings the first cell as activecell
    End If
Loop
End Sub

Why not preference like Do Until ActiveCell <> ActiveCell.Next in Usedrange. Otherwise How to restrict this around only the usedrange?

 

[hippiehacker]

here is the code to get the column number based on your requirements but i haven't understood what exactly you want to merge can you post example how it should look afterwards.

Sub pra()
For i = 1 To Range("A1").End(xlToRight).Column
j = i
Do
 i = i + 1
Loop Until Cells(1, i).Value <> Cells(1, i + 1).Value Or Cells(2, i).Value <> Cells(2, i + 1).Value
Next i
End Sub

 

[prabha_friend]

[TABLE="width: 1916"]
<tbody>[TR]
[TD="colspan: 10"]AU
[/TD]
[TD="colspan: 10"]BD
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Agents
[/TD]
[TD="colspan: 2"]Cash Application
[/TD]
[TD="colspan: 2"]Collections
[/TD]
[TD="colspan: 2"]Credit Application
[/TD]
[TD="colspan: 2"]Customer Master
[/TD]
[TD="colspan: 2"]Agents
[/TD]
[TD="colspan: 2"]Cash Application
[/TD]
[TD="colspan: 2"]Collections
[/TD]
[TD="colspan: 2"]Credit Application
[/TD]
[TD="colspan: 2"]Customer Master
[/TD]
[/TR]
[TR]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[TD]Internal
[/TD]
[TD]External
[/TD]
[/TR]
</tbody>[/TABLE]