Paul

Dim lrow As Long, lcol as Long

lrow = Cells(Rows.COUNT, "A").End(xlUp).Row
' change to what ever column you want.

lcol = ActiveSheet.Range("IV1").End(xlToLeft).Column
' change to what ever row you want

--
sb
"Paul Hastings" wrote in message
...
Hi -

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:=xlDatab ase, 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

Paul,

lrow determines the row number of the last entry in column A regardless of
any gaps in the data. You can choose any column you want to represent your
data.

lcol determines the column number of the last entry in row 1 regardless of
any gaps in the data. You can choose any row you want to represent your
data.

Than in your formula
add (just for clarity)
Dim rng as String
rng = "SP1a!R1C1:R" & lrow & "C" & lcol & ")"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=rng.CreatePivotTable TableDestination:="",

double check the "s and )
I wasn't sure if I got them right.

--
sb
"Paul Hastings" wrote in message
...
Steve -

I am not sure what this does. I am looking for the last row and column
that
have data in them. Then I want to convert that to rowcolumn format, e.g.
R1C1:R623C21. How would you go about that?

Thanks,

Paul
"steve" wrote in message
...
Paul

Dim lrow As Long, lcol as Long

lrow = Cells(Rows.COUNT, "A").End(xlUp).Row
' change to what ever column you want.

lcol = ActiveSheet.Range("IV1").End(xlToLeft).Column
' change to what ever row you want

--
sb
"Paul Hastings" wrote in message
...
Hi -

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:=xlDatab ase,
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

Steve -

I get it. Took a while for the cobwebs to clear, but I got it. Very nice.

Thanks,

Paul

Paul,

You're welcome! I use this all the time.

I also use defined names that will self-adjust and reference the name rather
than the range. (don't know how to adjust for column in the name, but can
adjust for row). Saves a lot of headache.

Post back if you want more.

--
sb
"Paul Hastings" wrote in message
...
Steve -

I get it. Took a while for the cobwebs to clear, but I got it. Very nice.

Thanks,

Paul

Steve,

Permission to email you? I have a situation similar to Paul's, with the
addition that the first row and column cell references are unknown.

Basically, if you have a modest sized table ANYWHERE on the worksheet, I
need to find the top-left cell reference and the bottom-right cell reference
for that table.

Thanks!

-gk-


"steve" wrote in message
...
Paul,

You're welcome! I use this all the time.

I also use defined names that will self-adjust and reference the name
rather
than the range. (don't know how to adjust for column in the name, but can
adjust for row). Saves a lot of headache.

Post back if you want more.

--
sb
"Paul Hastings" wrote in message
...
Steve -

I get it. Took a while for the cobwebs to clear, but I got it. Very
nice.

Thanks,

Paul

TBA,

Fire away (no virus please).

Let me know how you get to the table.

You can start with this code. If the active cell is anywhere in the table

Dim msg As String
'
Selection.CurrentRegion.Select
msg = MsgBox(Selection.Address)

--
sb
"TBA" wrote in message
...
Steve,

Permission to email you? I have a situation similar to Paul's, with the
addition that the first row and column cell references are unknown.

Basically, if you have a modest sized table ANYWHERE on the worksheet, I
need to find the top-left cell reference and the bottom-right cell
reference
for that table.

Thanks!

-gk-


"steve" wrote in message
...
Paul,

You're welcome! I use this all the time.

I also use defined names that will self-adjust and reference the name
rather
than the range. (don't know how to adjust for column in the name, but
can
adjust for row). Saves a lot of headache.

Post back if you want more.

--
sb
"Paul Hastings" wrote in message
...
Steve -

I get it. Took a while for the cobwebs to clear, but I got it. Very
nice.

Thanks,

Paul