Subtotal Issue

[longhair]

afternooon all,
i have what i hope is a simple query. a colleague is attempting to subtotal data in a spreadsheet but excel is reporting back that it cannot determine the header row.

cells.select
selection.subtotal grooupby:=6 function:=xlcount ....

row 1 is header row (bold, italicized, different backround, etc.).
row 2 is blank.
row 3 starts the data to subtotal.
is there anyway to set the header row programatically?
thanks in advance.
regards,
longhair

 

[SkipVought]

hi,

"row 2 is blank."

No! No! for tables & lists.

No Blank rows.

No Blank Columns

ONE heading row.

Skip,

_[red][/red]

 

[longhair]

SkipVought,
list originally did not have row2 as blank.
we're trying anything and everything to figure this out.
when removing the blank row it works sometimes and other times still throws the:
ms excell can't determine...
regards,
longhair

 

[SkipVought]

What kind of aggregation are you performing? COUNT?

Normally the wizard can easily detect the header row because its the only row without NUMERIC DATA.


Skip,

_[red][/red]

 

[longhair]

SkipVought,
i'll find out as soon as i can.
we figured it should be able to detect the header row easy - even followed ms help which suggests the changes to the header row (bold, italics, underlining, different background, etc.)
as far as i know there are no numbers in the header row but will check that out also. am also going to make sure that the sheet is properly sorted.
regards,
longhair

 

[SkipVought]

Your table also needs to be ISOLATED from other data on the sheet if there is any.

Check HELP on Guidelines for entering data on a worksheet

Skip,

_[red][/red]

 

[longhair]

SkipVought,
data on the sheet is all the data used - cells.select
xlcount is what he is trying to accomplish.
strang thing is - if he runs the macro manually (from the menu) seems to work just fine. when he tries to automate (call from a scheduled task / .bat job) it asks the question. regards,
longhair

 

[longhair]

well, think we have a solution.
run 90% of the macro - do a save as and close, open it back up and then the last 10% (subtotal part) works fine.
my guess is that because the macro creates the spreadsheet, from an imported text file, that it's choking on the subtotal because it's not saved as an .xls file yet. but all the other macro work seems to go fine.
regards,
longhair

 

[firefytr]

No, that is not the reason longhair. Post all of your code in it's entirety.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci