VBA to Create Grouping (Difficult)

[Biz]

Dear All,

Before Worksheet tab refers to Group Accounts before Grouping. After Worksheet tab refers to Group Accounts after Groupings that must be created by VBA.

I have posted this question in Mr Excel Forum
http://www.mrexcel.com/forum/showthread.php?t=561787

Your help would be appreciated.

Biz

[Excel Fox]

Biz, can you please take us through the flow here. Not able to follow how you reach from Before to After

[Biz]

Hi,

I have tried to group all expenses based Parent - Child relationship. If I click on Level 2 on After Worksheet tab it tells me all children belonging to a Parent.
1000 Group Accounts
41 Profit and Loss
39 Net Surplus / (Deficit) After Tax
42 Net Operating Surplus / (Deficit)
45 Net Operating Expenditure
88 Operating Expenditure
142 Operating Revenue

43 Net Non-Operating Surplus / (Deficit)
47 Non-Operating Expenditure
48 Non-Operating Income
40 Income Tax Expenditure
44 Income tax expense

6 Liabilities & Equity
28 Liabilities
30 Non-Current Liabilities
31 Current Liabilities

29 Ratepayers equity
32 General equity
33 Restricted equity
34 Designated funds and reserves
35 Targeted rate reserves
36 Other reserves
37 Revaluation reserve
38 Minority interest
7 Asset
8 Assets
9 Non-Current Assets
11 Property Plant and Equipment
12 Investment Property
13 Right To Acquire Assets
14 Intangible Assets
15 Goodwill
16 Livestock
17 Investments In Subsidiaries
18 Investment In Associates
19 Other Financial Assets
20 Derivative Financial Assets

10 Current Assets
21 Trade and Other Receivables
22 Inventories
23 Other Financial Assets
24 Derivative Financial Assets
25 Non-Current Assets Held For Sale
26 Income Tax
27 Cash and Cash Equivalents
169 Investment

For instance, Operating Expenditure includes Expenditure On Activities which includes following:
Staff
Professional Services
Contractors
Office Consumables
Occupancy and Utilities
Repairs and Maintenance
Insurance
Depreciation and Amortisation
Management Fees
Inter-Entity Funding
Grants and Sponsorship
Cost Of Goods Sold
Other Expenditure On Activities
Internal Charges and Allocations
Settlements


If I click on level 1 in After Worksheet tab it gives consolidated accounts structure.

Biz

[Excel Fox]

So how does one know which row to show and which to hide? In other words, what is the logical flow to create the groups?

[Biz]

Hi

I have tried creating macros but they are not working properly as it does not give results as per After Worksheet tab.

Sub Blanks()
  
'Remove Bolds on Blank Cells
    Cells.Select
    Selection.SpecialCells(xlCellTypeBlanks).Font.Bold = False
 
End Sub

Sub Check()
'
' 
'
Dim RowCount As Integer
Dim RowNo As Integer
Dim iColNo As Long
Dim LastCheck As Integer
Dim FirstCheck As Integer
Dim CalcFlag As Boolean

'Count number of rows
RowCount = ActiveSheet.UsedRange.Rows.Count

'Initialise the range
LastCheck = 4



For iColNo = 3 To 9
For RowNo = 2 To RowCount

If Cells(RowNo, iColNo).Font.Bold = True Then
     FirstCheck = RowNo - 1
End If

If FirstCheck > LastCheck Then
Rows(FirstCheck & ":" & LastCheck).group
'MsgBox (FirstCheck & "and" & LastCheck)
Cells(RowNo, iColNo).Font.Underline = True
'Selection.Rows.Group
LastCheck = FirstCheck + 2
End If
Next RowNo
LastCheck = LastCheck + 1
Next iColNo


End Sub

Structure
Grouping Starts one cell below the bold and grouping ends before next bold. For instance, Col I relates to Staff Permanent grouping which
starts from row 10 and finishes on row 28.

Looping structure would loop through I to D.


End goal is to have structure below at Level 1 grouping.

1000 Group Accounts
41 Profit and Loss
39 Net Surplus / (Deficit) After Tax
42 Net Operating Surplus / (Deficit)
43 Net Non-Operating Surplus / (Deficit)
6 Liabilities & Equity
28 Liabilities
29 Ratepayers equity
7 Asset
8 Assets
9 Non-Current Assets
10 Current Assets


From the above there grouping for Profit and Loss, Liabilities & Equity, Asset

Hope above helps.

Biz