Hey Simon!! Here are some examples:
Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Sub test()
With Sheets("Sheet1").Range("A1:A" & .Range( _
"A" & .Rows.Count).End(xlUp).Row).Copy Sheets("Sheet2").Range("A1")
End With
End Sub

Set StartRange and End Range:

Sub StartEnd()
Dim StartCell, EndCell As Range
Set StartCell = Range("A3")
Set EndCell = Cells(Rows.Count, "A").End(xlUp)
Range(StartCell, EndCell).Select
End Sub


Sub zeroo()
n = Cells(Rows.Count, "A").End(xlUp).Row
Range("A3:A" & n).Select
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Simon" wrote:

Hi everyone,

I am pasting data from e.g sheet1 into sheet2 once I've used VBA to
add an autofilter and remove the blanks (thanks to Ron's site) and on
the sheet (and cell range) where I am pasting it, I use that range in
the Pivot Table Wizard as the array for the Pivot table.

I use sheet3 for the Pivot Table and in VBA select a cell within the
table and use a PivotTable.Refresh to update the table.

I wish to copy out the data from the updated Pivot Table in VBA (the
amount of rows in the tbale will vary as the pasting in of data
changes). However I am unsure how to declare the variables
I was thinking, something like r = 2, last row = range("b65536).xlUp

Coudl you help please?