Quick/efficient way to VBA subtotal

[assertec]

Hi guys,

Each month I get sent spreadsheets that need to be subtotalled... these sheets can at times be about 50,000 - 60,000 rows because they haven't been subtotalled.

I wrote a macro that is basically automating the manual menu method (which although slow) works ok with Excel version 2007 and 2010... though crashes on XP/2003 because due to the subtotalling using the menu method it blows out these 50,000 - 60,000 rows to a limit that is far beyond what XP/2003 can handle as XP/2003 only allow 65536 rows max.

Basically what I'm after is code that subtotals amounts based on a particular stock number then leaves only ONE row for that particular stock number.... and not the other row values that make up that subtotal amount... and in doing so this number of records can then be handled by any version of Excel..

Thanks for any help in this.

Regards Karen

 

[Claus Busch]

Hi Karen,

Am Sun, 17 Nov 2013 03:31:24 -0800 (PST) schrieb (e-mail address removed):

Basically what I'm after is code that subtotals amounts based on a particular stock number then leaves only ONE row for that particular stock number... and not the other row values that make up that subtotal amount... and in doing so this number of records can then be handled by any version of Excel.

filter your stock numbers with advanced filter without duplicates to
another sheet and then calculate with SUMIF or SUMPRODUCT


Regards
Claus B.

 

[Karen]

Hi Karen,









filter your stock numbers with advanced filter without duplicates to

another sheet and then calculate with SUMIF or SUMPRODUCT





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus

Regards
Karen