VBA Excel Help!

[12369874]

Hello!

I want to be able to write a macro that will solve the following problem. I have a spreadsheet that looks like this:

Car Quantity
Nova 1
Nova 1
Nova 1
Astra 1
Astra 1

I want to be able to tally up all the quantities so my spreadsheet displays the info below, I need to know what code to write.

Car Quantity
Nova 3
Astra 2

Thanks in advance

Paul

 

[BrainB]

Use a Pivot Table. With practice - a half minute job on something like this. Regards
BrianB
** Let us know if you get something that works !
================================

 

[12369874]

Hi Brian

Thanks for the reply, however, I need to write a macro for this as the actual data I use is a bit more complicated, I download these reports from a bespoke database and hope to manipulate them in excel, the other problem I have is that the number of records is different every time so I need a statement that looks at the last row.

Cheers
Paul

 

[xlbo]

Sounds like a pivot table is still the way to go, just record yourself creating a pivot table on your actual data and then insert something like the following:

lRow = Sheets("YourDataSheetName&quot.range("A65536&quot.end(xlup).row


Then in the pivot table creation code, replace the source data range with

sourcedata:=sheets("Yourdatasheetname&quot.range("A1:Z" & lRow)
where your data runs from A to Z Rgds
~Geoff~

 

[Loomah]

Another option (not as pretty as a pivot table) is to use subtotals

eg

[a1].Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2

Assumes there is data in A1 and that your list isn't split in anyway.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[12369874]

Thanks guys! Ill give it a try