• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Subtotal on 30 and more columns through VBA? TotalList:=Array, Out of Memory?

C

Chirag R Raval

Member
Dear Sir,

I have below code for simply subtotal on various columns but fails after 30th column.
Data have total 65 columns , there are many columns require to subtotal but at the end of continues series at 30th column (though require or not required subtotal) , may be on 31st column Out Of Memory Error displayed

Code

Code: 
Sub SobTotRequ()
With ActiveSheet

.Range("A1:BL" & LastRow).Select
        With Selection

    .SUBTOTAL GroupBy:=2, Function:=xlSum, TotalList:=Array(14, 15, 19, 20, 21, 22, 23, 24, 27, 28, 29, 30, 32, 33, 34, 40, 41, 42, 43, 44, 45, 46), _
    Replace:=False, pagebreaks:=False, SummaryBelowData:=True
   
    ActiveSheet.Outline.ShowLevels RowLevels:=3
           
    End With
    End With
End Sub

hope there are some solution found here that can subtotal on any number of columns in one shot.

Regards,

Chirag Raval
 
That's entirely dependent on your workbook (i.e. row size etc), and any additional add-ins you've got running.

Typically speaking, prior to Excel 2013, 32 bit install of Excel, can only use up to 2Gb, including any process running in Excel instance.

For 2013 and later, memory limit was increased to 4Gb.

Without knowing full detail, hard to say, but I'm guessing your data is too large and eating up memory.
 
Dear Sir @Chihiro,

Oh another aspect found from your valuable tips.
Yes I have many add-in installed & system have 8 GB RAM with
64 Bit win 7 Os & 64 Bit office installed.

Oh that means my code not have any problem & yes
There are many rows for each sutotalable groups.

Now I need to tempararly disabled add-ins & check on few
Lines.

Surly I will revert after check.
Thank you very much for pointing possible
Aspects.

Regards,

Chirag Raval
 
Hi !

Even with 64 GB RAM Excel can't handle more than 2 or 4 GB
and as Excel is one of the worst application wasting RAM …

Try reducing the size of the array, who knows ?!
​
 
Dear Sir @Marc L,

Great twisting , & now I feel
I am In the trap of limitations & feel
Helpless. Why Microsoft bound excel in this
4gb limitation? How can extend its reserve
Memory usage?

At about 65 columns ,I not required
All colums"s sub total. Even required colums count
Is less then 30 but excel stop subtotal at column
No 30 and display "Out of Memory"(though it's complete
All procedures till maco's end) & after complete macro
This error displayed & we can see it subtotal
Till column no 30& not afterwards.


So I must reduce required subtotal on columns array?.

Can we construct it to complete 2 or more parts?
That just look like complete in one run ?.

I search before posting this thread on web but
Surprisingly not found any single user require
Many column's subtotal because there not available
Any guideline on web that how sucessfully subtotal
On 40, 50 or more columns.

Oh I forgot to mention wonder that if I manually
Do subtotal ,though error appear & also sheet looking weird just before screen auto update about 1 to 2 seconds , we just need scroll little up -Down to force screen update but
It sucessfully subtotaled on all tick marked
Columns. So can we say VBA way feels limitations?

Point to be noted me lords that on record maco
Can we say that its required fully qualified range (explicitly mention Endd range)?
Like In recorded macro "A1 :BL5000"? And not work if you
Give dynamic "LastRow" variable?

Can it work if we store our range in object & set it as object as fully qualified range object?

I now really confused.

Please help.

Regards,

Chirag Raval
 
Last edited:
Chirag R Raval said:
How can extend its reserve Memory usage?

So I must reduce required subtotal on columns array?
Click to expand...
As Excel needs to load all in memory, create a one worksheet only
new workbook and just paste data values, no conditional formatting
no formula, no ...

Yes, try reducing the array.

Chirag R Raval said:
Can we say that its required fully qualified range (explicitly mention Endd range)?
Like In recorded macro "A1 :BL5000"? And not work if you
Give dynamic "LastRow" variable?

Can it work if we store our range in object & set it as object as fully qualified range object?
Click to expand...
"Dynamic" means nothing as a range is just a range,
so whatever the range reference ...
​
 
You really need all those columns? In most cases, you should limit report to just the information users can easily digest and gives info on making business decision etc. I rarely exceed 15 or so columns in a single report (excluding data models of course).

As Marc has stated, limit formatting and other visual fluffs to minimum. I suspect that you have bunch of those in your worksheet, which is eating up resource.
 
Dear Sir @Marc L & Sir @Chihiro,

Thank you very much for your kind effort..

Its resolve now .
As I reduce number of columns which cover under subtotal process.
And ,
Obviously , my little mistake also to structured my range as I cover some blank columns after filled range , & also try through code, subtotal about 2 columns which contain text..(sorry for that, & whole thread bee generated above Out Of Memory..).

Now ,
Thank you very much for your co-operation due to / because of plenty of concepts we are in touch to learn from this thread.

Regards,

Chirag Raval
 
You must log in or register to reply here.
Back
Top