macros and pivot tables... help please!

[white_rhino]

hi robert,

the problem with using a greater database, such as "R1000", is tha
since the number of rows with data changes, the number of unpopulate
rows / rows that you will have to hide each month will change as well
there's no way to be sure which row to start hiding from unless yo
count the number of rows.

hi artin,

i understand that

....SourceData:="BFCCL!R1C1:R" & i &"C9").CreatePivotTable

has the proper syntax and the variable, but i don't exactly understan
what the first line of code,

i=counta(sheets("BFCCL").Rows(3) 'or 1,2

is for or where i should put it. i know that it defines the variabl
for the second part, but what exactly does Rows(3) or 1,2 stand for?

thanks again for your help and input so far.

- fre

 

[Artin]

hi, fred
sorry, it should be .Columns(*)
i=counta(sheets("BFCCL").Columns(3) 'or 1,2
is used for counting how many rows in the source data range.
for example:
A B C
1 a 2 1.5
2 b 3 1.2
..
..
10 b 1 2.4

you can use i=counta(sheets("BFCCL").Columns(3) to get i=10
if there are some blank cells in column C, you can use .Columns(1) or
..Columns(2) to caculate the number of rows.