shahidsamikhan
New Member
- Joined
- Dec 4, 2014
- Messages
- 17
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
Output sheet:
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 |