Find Specific Value from Cell and Merged with Adjacent Cell

[shahidsamikhan]

Hi All,
I have a excel sheet where column A have sub total for each category. (No. of rows in attached sample file is not fixed)

I want to find sub total and merge that cell with adjacent cell then align entire row to center and align merged cell to left alignment with proper border formatting as showed in sample file.

I need solution in vba code.

Regards,
Shahid

Sample.xlsx
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	TOTAL
2	AAA	AAA-1	1	1	8	5	2	1	18
3		AAA-2	2	1	2	5	4	6	20
4	Sub-Total		3	2	10	10	6	7	38
5	BBB	BBB-1	2	5	2	1	8	9	27
6		BBB-2	1	1	8	5	2	1	18
7		BBB-3	1	1	8	5	2	1	18
8		BBB-4	2	1	2	5	4	6	20
9	Sub-Total		6	8	20	16	16	17	83
10	CCC	CCC-1	1	1	8	5	2	1	18
11		CCC-2	2	1	2	5	4	6	20
12		CCC-3	2	5	2	1	8	9	27
13	Sub-Total		5	7	12	11	14	16	65
14	DDD	DDD-1	2	1	2	5	4	6	20
15	Sub-Total		2	1	2	5	4	6	20
16	EEE	EEE-1	2	5	2	1	8	9	27
17		EEE-2	1	1	8	5	2	1	18
18	Sub-Total		3	6	10	6	10	10	45
19	FFF	FFF	2	1	2	5	4	6	20
20	Sub-Total		2	1	2	5	4	6	20
21	Grand Total		21	25	56	53	54	62	271
Actual

Cell Formulas
Range	Formula
C18:I18,C4:I4	C4	=SUM(C2:C3)
C9:I9	C9	=SUM(C5:C8)
C13:I13	C13	=SUM(C10:C12)
C20:I20,C15:I15	C15	=SUM(C14)
I19,I16:I17,I14,I10:I12,I5:I8,I2:I3	I2	=SUM(C2:H2)
C21:I21	C21	=C4+C9+C13+C15+C18+C20

Output sheet:

Sample.xlsx
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	TOTAL
2	AAA	AAA-1	1	1	8	5	2	1	18
3		AAA-2	2	1	2	5	4	6	20
4	Sub-Total		3	2	10	10	6	7	38
5	BBB	BBB-1	2	5	2	1	8	9	27
6		BBB-2	1	1	8	5	2	1	18
7		BBB-3	1	1	8	5	2	1	18
8		BBB-4	2	1	2	5	4	6	20
9	Sub-Total		6	8	20	16	16	17	83
10	CCC	CCC-1	1	1	8	5	2	1	18
11		CCC-2	2	1	2	5	4	6	20
12		CCC-3	2	5	2	1	8	9	27
13	Sub-Total		5	7	12	11	14	16	65
14	DDD	DDD-1	2	1	2	5	4	6	20
15	Sub-Total		2	1	2	5	4	6	20
16	EEE	EEE-1	2	5	2	1	8	9	27
17		EEE-2	1	1	8	5	2	1	18
18	Sub-Total		3	6	10	6	10	10	45
19	FFF	FFF	2	1	2	5	4	6	20
20	Sub-Total		2	1	2	5	4	6	20
21	Grand Total		21	25	56	53	54	62	271
Required

Cell Formulas
Range	Formula
C18:I18,C4:I4	C4	=SUM(C2:C3)
C9:I9	C9	=SUM(C5:C8)
C13:I13	C13	=SUM(C10:C12)
C20:I20,C15:I15	C15	=SUM(C14)
I19,I16:I17,I14,I10:I12,I5:I8,I2:I3	I2	=SUM(C2:H2)
C21:I21	C21	=C4+C9+C13+C15+C18+C20

 

[Marc L]

Hi, according to your attachment a VBA demonstration as a beginner starter without merging cells :​

Sub Demo1()
           Dim Rf As Range, R&
    With [A1].CurrentRegion.Rows
           Set Rf = .Columns(1).Find("*Total")
        If Not Rf Is Nothing Then
                Application.ScreenUpdating = False
                [A2].Resize(.Count - 1).HorizontalAlignment = xlLeft
                R = Rf.Row
            Do
                With .Item(Rf.Row).Columns
                     .Font.Bold = True
                     .Item("A:B").HorizontalAlignment = 7
                     .Item("C:I").HorizontalAlignment = xlCenter
                End With
                   Set Rf = .Columns(1).FindNext(Rf)
            Loop Until Rf.Row = R
               .Borders.Weight = xlThin
                Application.ScreenUpdating = True
                Set Rf = Nothing
        End If
    End With
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find Specific Value from Cell and Merged with Adjacent Cell - OzGrid Free Excel/VBA Help Forum
also Find Specific Value from Cell and Merged with Adjacent Cell
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.