VBA bit,

Range(“A3:I256”)

This works fine when the pivot table has a data source of A3 to I256, problem is how do i get this to change.

i have used rows.count to populate a variable and find the end of the data not sure how i can change the 256 to be rows.count.

anyone help.

What is the correct syntax.

use offset to give you a variable range.

managed to get it to work, create a text variable, and concatenate the text/variable to create the range variable.

Seems to work.

using “indirect?”

I find that instead of using a string for the Range reference it’s easier to use cells eg; this code colours cells A1:J1 in yellow, you can easily use any integer count to modify the range through the Cells(rows,columns)


Public Sub tester()
Dim myRange As Range
Set myRange = ActiveWorkbook.ActiveSheet.Range(Cells(1, 1), Cells(1, 10))
myRange.Interior.ColorIndex = 6
End Sub


rowsfd = Worksheets(“FD”).UsedRange.rows.Count
j = “Z” & rowsfd
pivotfd = “A3:” & j
Range(pivotfd).Select

Next query

I am trying to create a pivot table. This doesn’t work, but if i define a named range in Excel and use that as the range for sourcedata it works. So how am i using Sourcedata incorrectly?

Sub pivotroof()
Dim PC As PivotCache
Dim PT As PivotTable
Set flooringdata = Worksheets(“FD”).Range(“a3:z100”)
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=(flooringdata))
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PC, Tabledestination:=””)

I think that the sourcedata variable is string only so just use the “FD!A3:Z100” string instead