Need help to select data range for pivot table

[ANKUSHRS1]

I m trying to creat a pivot trable by vba. I need help to select data range for this table.
data range always variable depending on work volume. it is dynamic. I have write code as below code for data range.
[Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "'Format - Output'!A1:BF673")]

Thanks in advance.

 

[Marc L]

See UsedRange, CurrentRegion and Address in VBA inner help …
​

 

[Niraj Baraili]

Another approach
Create a dynamic named range with the below formula:

OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($A$1:$N$1))
Where A1 is your starting data point, COUNTA($A:$A) will give you the number rows and COUNTA($A$1:$N$1)) is the number of columns depending on your data.

Lets say, named range is Pivot_Range.

Now go to change data source and put the named range 'Pivot_Range'.
Then use the below code for refreshing pivot -

With Sheets("sheet1")
.PivotTables("PivotTable3").PivotCache.Refresh

End With

 

[rumshar]

Consider converting your range to Table. Combination of excel table and pivot table in awesome. This way you needn't to worry about your data expansion/contraction.