Macro to merge different cell rages based on values of another range

[nbuddhi]

Dear Team,

I have a data sheet receive from other company and I have to make a different report using the said sheet. most of the sections they have already merged across.

There are 4 sections called Data 1, Data 2, Data 3, & Data 4. Data 1 sections divided into 3 sub sections. I have to identify the similar values in section, Data 4 and merge independently the section, Data 1 (3 sub sections separately) & Data 2. Sample data sheet attached for your easy reference with the data in original states and expectation sheet for clarity. Data will be filled from row 21 to 142.

Thanks & Best Rgds,
nbuddhi.

 

[Marc L]

most of the sections they have already merged across.

Such a bad idea ! Do you have the original workbook without any merged cells ?​

​

 

[nbuddhi]

Dear Marc,

Most of the cells are merges as top area of the concern area having more data in individual cells, so that maintain the table appearance they have merged I think. Anyhow I can extract the data into a sheet like attached one removing merge columns.

Thanks & Best Rgds,
nbuddhi

 

[Marc L]

According to your initial post attachment a beginner starter VBA demonstration to paste only to the Sheet1 (before) worksheet module :​

Sub Demo1()
        Application.DisplayAlerts = False
        Columns("V:AK").Hidden = True
    For L& = 21 To UsedRange.Columns("AL:AN").Find("*", , , , , 2).Row
            F& = L
            While Cells(L, 38) = Cells(L, 38)(2):  L = L + 1:  Wend
        With Rows(F & ":" & L).Columns
            .Item("G:I").Merge
            .Item(10).Merge
            .Item("K:M").Merge
            .Item("N:P").Merge
            .Item("AL:AN").Merge
        End With
    Next
        Application.DisplayAlerts = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[nbuddhi]

Dear Mark,

Thank you it worked perfectly with adding below two dim statements. Appreciated your help always.

Dim L As Long
Dim F As Long

Thanks & Best Rgds,
nbuddhi

 

[Marc L]

Dear Mark

As the variables are already Long data type - a VBA help must see ! - in my demonstration as it is​

so the Dim statement is necessary only with Option Explicit in the module​

so next time attach a binary .xlb workbook rather than a .xlsx …​

​

 

[nbuddhi]

Dear Marc,

Well noted your guide. Thank you

Best Rgds,
nbuddhi