Can't make "Selection.Subtotal..." dynamic

[lemo]

hi.
i am using the following static Subtotal function in my macro -

Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(6), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True

i tried to make that "6" dynamic, but surprisingly got an error message.
the code i used is something like this -

width = WorksheetFunction.CountA(Range("1:1"))
Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(width), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True

am i missing something obvious here?
thanks,
len

 

[Brianwarnock]

Is that the correct use of the Width function?

 

[lemo]

i am sorry, i didn't mean to use the reserved word, i just put it here because it is a meaningful name for this variable.
in actuality, i used something like "wdth", or "wd" as that variable name, so the code is

wd = WorksheetFunction.CountA(Range("1:1"))
Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(wd), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True

l

 

[Brianwarnock]

I'm sorry but I am not telepathic, I have no idea what your error message is, what the rest of your code looks like, or your data.

Brian

 

[Brianwarnock]

Hmm looks like Lemo is not coming back. For anybody hitting this thread on a search it is my guess, and it can only be a guess, is that the number of columns picked up by wd is greater than those in the selection for the subtotal.


Brian

 

[lemo]

Brian, i am so sorry i was mia for a while - just got too busy, excuses excuses..

and i feel double-guilty now - the macro works, without me doing much - i copy-pasted some pieces of code back and forth after reading this just now, and it clicked. not sure what the problem was either, i didn't keep the exact code that was giving me problems.

yep, just wasted a chunk of your time. as an attempt at atonement, posting the working code, although it looks pretty rudimentary now, but maybe can help somebody to start things off. sorry again, and thanks for help..

this will operate on a block of data, starting in A1, names in first row. it will
- insert a blank column "Priority" up front,
- run data subtotal, grouping on the 2-nd column (B) and summarizing by count,
- set various print criteria (including page breaks after each subtotal).

Sub PrintSubtotalsMacro()

'Inserts column "Priority", subtotals, sets printing criteria for X report.

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 12
Range("A1") = "Priority"

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintTitleRows = "$1:$1"
.PrintGridlines = True
End With

Range("a1").Select
wdth = WorksheetFunction.CountA(Range("1:1"))
Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(wdth), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True

hght = WorksheetFunction.CountA(Range("b:b")) - 1 'don't need Grand Count printed hence -1
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(hght, wdth)).Address

End Sub