Solved

passing array in subtotal

Posted on 2011-03-16
4
328 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
[X]
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
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

737 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