format a report using VBA & add totals & subtotals

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

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

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?)

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?)

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

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

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

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

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

Can you give me a quick example?

Thanks,
Barrie

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

Can you give me a quick example?

Thanks,
Barrie

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

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

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

Thanks,

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

Thanks,

But ...

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

PS......thanks for the input

But ...

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

PS......thanks for the input

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

Thanks,

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

Thanks,