Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

That's the function I use. Hope it helps

Die_Another_Day
ecctse wrote:
Hi again Die_Another_Day,

You're a real champ. Your codes work perfectly. Just 1 more question: where
can I locate your FindLastRow object?

Very much appreciated & have a great weeekend.
Eric


"ecctse" wrote:

Hi,

I am trying to create a pivot tables using VBA with different number of rows
of source data. How can I supply the number of rows as a parameter to the
following method:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:="???"

It seems it doesn't like me to supply a string variable to the SourceData,
for example, SourceData:= strRange

Any other method to get around? Thanks.