Merge cells convert to normal

[makinmomb]

I have following data

[TABLE="width: 1305"]
<colgroup><col><col><col span="6"><col></colgroup><tbody>[TR]
[TD="colspan: 9"]TRANSROAD - 01/06/18[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]JOB NO: [/TD]
[TD]PORT CHARGES [/TD]
[TD] USD [/TD]
[TD] APPROVED [/TD]
[TD]DIFF[/TD]
[TD] TSHS [/TD]
[TD] APPROVED [/TD]
[TD]DIFF[/TD]
[TD]REMARKS [/TD]
[/TR]
[TR]
[TD]TR-0418[/TD]
[TD]KALONJI - P/C [/TD]
[TD] 76[/TD]
[TD] 76[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TR-0460[/TD]
[TD]FALAY - P/C [/TD]
[TD] 117[/TD]
[TD] 117[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TR-0454[/TD]
[TD]PEARL - P/C [/TD]
[TD] 117[/TD]
[TD] 117[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TR-0377[/TD]
[TD]LUNGA - P/C [/TD]
[TD] 117[/TD]
[TD] 117[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TR-0184[/TD]
[TD]OM METAL - CHEMICAL - REM+STOR [/TD]
[TD] 384[/TD]
[TD] 384[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[makinmomb]

I want the macro to swim into header that have merged cells and bring that data to a1 , so that i can do some data
pull since the merged cells is bringing problems for data pulling

 

[makinmomb]

Search range entire sheet

 

[MARK858]

Sub Unmerge()
Cells.UnMerge
End Sub

 

[makinmomb]

mark858 thank you it did exactly what i needed , cant imagine how long this would have been without a macro

 

[MARK858]

cant imagine how long this would have been without a macro

2 seconds, you click the square where the column letters and row numbers meet and click the merge and center icon

Anyway happy it worked for you