VBA Pivot Tables with unkown # of rows in the data set

  • Thread starter Thread starter Tbone_Scott
  • Start date Start date
T

Tbone_Scott

I am trying to create a macro that creates a pivot table. Problem is
the # of rows of data is always variable (# of columns is always
constant).

Does the range of the Pivot table have to be explicitly defined, as
below:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Data'!*R1C1:R2500C42*",

Or can the RC:RC reference be variable, and depend on the # of rows?

I have tried concatenating the RC:RC reference, such as:

"'Data'!R1C1:R" & x & "C42",
where x = rowcount(Columns("A:A")) (rowcount is a UDF that counts the #
of rows of data).

But that doesn't work. Any ideas anyone?
 
You could use a dynamic formula to name the source range, e.g. Database.
Then, use the range name in the code --

SourceData:="Database"
 
Back
Top