Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

passing array in subtotal

Posted on 2011-03-16
4
326 Views
Last Modified: 2012-05-11
I need to pass array in subtotal
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array( 1 to lastcolumn ), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True

I am doing the above fn. Please help and let me know how to pass 1 to lastcoulmn in this array
0
Comment
Question by:Ruchi_Sas
  • 2
4 Comments
 

Author Comment

by:Ruchi_Sas
ID: 35147135
does any one has answer to this question? If yes, then please answer
0
 
LVL 39

Accepted Solution

by:
nutsch earned 125 total points
ID: 35152833
You can do something like this. Probably not the most efficient, but it should do.

Dim arrSeries(), lgMin As Long, lgMax As Long

lgMin = 2
lgMax = 6

ReDim Preserve arrSeries(lgMax - lgMin)

For i = lgMin To lgMax
    arrSeries(i - lgMin) = i
Next

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=arrSeries, Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Open in new window


Thomas
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35152839
something like this with the last column (you can't put the first column since you're grouping on it)

Sub Macro3()
'
' Macro3 Macro
Dim arrSeries(), lgMin As Long, lgMax As Long

lgMin = 2
lgMax = Cells(1, Columns.Count).End(xlToLeft).Column

ReDim Preserve arrSeries(lgMax - lgMin)

For i = lgMin To lgMax
    arrSeries(i - lgMin) = i
Next

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=arrSeries, Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub

Open in new window

0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question