Automatically Determine Last Row and Column

P

Paul Hastings

Hi -

I am working in Excel and I am trying to automatically generate a Pivot
Table from data that has been
pulled into a worksheet from a database. The data always starts in R1C1, but
the extent is variable. Here is an example:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SP1a!R1C1:R6897C21").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"

Its the "R6897C21" part I want to fix. How can I determine the last row and
column that has data in it so I can change this based on my actual data?

Thanks,

Paul Hastings
Principal Computer Scientist
Jorge Scientific Corporation
104 Park Drive
Warner Robins, GA 31088
(478) 923-2662 x122
 
J

Jean-Paul Viel

Add to your code:

Dim lngR As Long
lngR = Range("a1").CurrentRegion.Rows.Count
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & lngR & "C14").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable range in PivotTable 0
Variable Address in Pivot Table VBA code 4
Creating a Pivot Table via Macro - Errors on Recorded Code 3
Pivot table 2
Macro to create pivotchart problems 0
syntax in VBA to create a pivot table 2
Pivot Table Code Fails 1
Format Pivot total rows 2

Top