Create a Report in Excel using VBA

[Rasool Bux]

Dear All,

I need help to create a report/sheet from the data with subtotals of three levels (cluster_no, District, Province) using vba through a button in excel. The data entry was done by subcluster level (uploaded file). This is a sample of just three columns and actual data contains more than 50 columns in a table range.

Please help me in this regard.
Thanks

 

[Chihiro]

Why not use pivottable? It's really meant for this type of operation.

 

[Hui]

And control the PT using VBA?

 

[Rasool Bux]

Thanks for suggestions. I know through Pivot table, but I want to make a button to add rows of subtotal in the same sheet for different levels using VBA.

Please help me in this regard.
Best Regards,
Rasool Bux

 

[Chihiro]

What's your expected output. Construct manually and upload.

If using Subtotal Grouping available in Excel... remember that it can't be used on Table range and it must first be converted to regular range. (By the way, multi-level subtotal grouping will look messy).

You can also use macro recorder for the process and modify to suite your need.

 

[Rasool Bux]

I have done calculations manually just for one province. The subclusters are not fixed for each cluster i.e. 2, 3, 4 subclusters per cluster.

Please see the uploaded file.

 

[Chihiro]

How about this format. It uses native Excel Subtotal Grouping.

And can easily be reversed to return to original format.

Code will be like below.

Sub rangeSubTotal()

Application.ScreenUpdating = False

With ActiveSheet.Cells(1).CurrentRegion
    .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 6, 7), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    .Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(5, 6, 7), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End With

ActiveSheet.Cells(1).CurrentRegion.ClearOutline

Application.ScreenUpdating = True

End Sub

Sub revertSubTotal()

ActiveSheet.Cells(1).CurrentRegion.removeSubTotal

End Sub

See attached.

 

[Rasool Bux]

Thank you very much, that's what I want but I have more than 50 columns. how I can use if I have range table instead of array(5,6,7) with header row.

I have created a userform for data entry of that table.

 

[Chihiro]

So each number in Array() represents column# within range that SUBTOTAL calculation is done on.

Replace with array of column# for your actual table.

Same goes for "GroupBy:=#" replace with actual column# where info is stored.

 

[Chirag R Raval]

Dear Sir,

Why there are out of memory error displayed?
more in below link.

https://chandoo.org/forum/threads/h...ough-vba-totallist-array-out-of-memory.38354/

Regards,
Chirag Raval