MrExcel Publishing
Your One Stop for Excel Tips & Solutions

format a report using VBA & add totals & subtotals


Posted by Mickael on September 07, 2001 9:10 AM

I would like to automatically format lines of rawdata using VBA in order to create a report with specific borders, spaces, column width... + add subtotals & totals to the report (the formats available using pivot tables do not correspond to my need). The raw data is to change every month (more or less lines / samme number of columns).
Could anyone give me line of code examples, usefull tips or point me to interesting resources ? Thank you.


Posted by Ferenc on September 07, 2001 9:21 AM

To select the entire data when you open the file, use
Range("A1", Range("A1").End(xlDown).End(xlToRight).Address).Select

You can then use the Macro Recording function to finish off the rest of your VBA code.

Hope this helps you out. If not, let me know and I'll help you out further.

Barrie

Posted by Barrie Davidson on September 07, 2001 9:23 AM

To select the entire data when you open the file, use
Range("A1", Range("A1").End(xlDown).End(xlToRight).Address).Select

You can then use the Macro Recording function to finish off the rest of your VBA code.

Hope this helps you out. If not, let me know and I'll help you out further.

Barrie

Posted by Sandor on September 07, 2001 9:38 AM

Just a minute ....


The entire data range is not necessarily selected by :-
Range("A1", Range("A1").End(xlDown).End(xlToRight).Address).Select

What would the code be, Barrie, for selecting the entire data range for all possible conditions that may exist on a worksheet?
(Also, why would you want to actually select the range?)

Posted by Sandor on September 07, 2001 9:38 AM

Just a minute ....


The entire data range is not necessarily selected by :-
Range("A1", Range("A1").End(xlDown).End(xlToRight).Address).Select

What would the code be, Barrie, for selecting the entire data range for all possible conditions that may exist on a worksheet?
(Also, why would you want to actually select the range?)

Posted by Barrie Davidson on September 07, 2001 9:49 AM

You are correct..

My code might not necessarily select all cells. I was assuming that the raw data had information in each cell of the data range. That is, no blank cells. If that is the case, my code would work. You could also use
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

As far as why I would want to select these cells, I assumed that Mickael would want to put sub-totals since he indicated this. You would need to select all cells to add sub-totals, using Excel's sub-total function.

Regards,
Barrie

Posted by Barrie Davidson on September 07, 2001 9:49 AM

You are correct..

My code might not necessarily select all cells. I was assuming that the raw data had information in each cell of the data range. That is, no blank cells. If that is the case, my code would work. You could also use
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

As far as why I would want to select these cells, I assumed that Mickael would want to put sub-totals since he indicated this. You would need to select all cells to add sub-totals, using Excel's sub-total function.

Regards,
Barrie

Posted by Sandor on September 07, 2001 9:59 AM

With VBA, not necessary to select the cells to add sub-totals.

Posted by Sandor on September 07, 2001 9:59 AM

With VBA, not necessary to select the cells to add sub-totals.

Posted by Barrie Davidson on September 07, 2001 10:08 AM

Re: With VBA, not necessary to select the cells to add sub-totals.

Can you give me a quick example?

Thanks,
Barrie

Posted by Barrie Davidson on September 07, 2001 10:08 AM

Re: With VBA, not necessary to select the cells to add sub-totals.

Can you give me a quick example?

Thanks,
Barrie

Posted by Ivan F Moala on September 07, 2001 10:26 AM

Re: With VBA, not necessary to select the cells to add sub-totals.

Thanks,

Heres a quick ex. Barrie

Sub Test()
Dim SumRg As Range
Dim Result as Double

Set SumRg = Range("A1", Range("A1").End(xlDown).End(xlToRight).Address)

MsgBox "Sum of the range [" & SumRg.Address & "] = " & WorksheetFunction.Sum(SumRg)

End Sub


Ivan

PS.....thanks for helping out

Posted by Ivan F Moala on September 07, 2001 10:26 AM

Re: With VBA, not necessary to select the cells to add sub-totals.

Thanks,

Heres a quick ex. Barrie

Sub Test()
Dim SumRg As Range
Dim Result as Double

Set SumRg = Range("A1", Range("A1").End(xlDown).End(xlToRight).Address)

MsgBox "Sum of the range [" & SumRg.Address & "] = " & WorksheetFunction.Sum(SumRg)

End Sub


Ivan

PS.....thanks for helping out

Posted by Sandor on September 07, 2001 10:37 AM

Rows("1:10").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(1)

Thanks,

Posted by Sandor on September 07, 2001 10:37 AM

Rows("1:10").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(1)

Thanks,

Posted by Sandor on September 07, 2001 10:57 AM

But ...


Your macro does not produce sub-totals, which is what Barrie was asking about.


PS......thanks for the input

Posted by Sandor on September 07, 2001 10:57 AM

But ...


Your macro does not produce sub-totals, which is what Barrie was asking about.


PS......thanks for the input

Posted by Barrie Davidson on September 07, 2001 11:11 AM

Sandor & Ivan, thank you for the input & examples :)

Thanks,

Posted by Barrie Davidson on September 07, 2001 11:11 AM

Sandor & Ivan, thank you for the input & examples :)

Thanks,