• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1582
  • Last Modified:

Select flexible range in VBA

Experts,

I have a question in VBA. How do we select a flexible range of cells?

For example, the following code will let me create a pivot table from the source data R2C2:R11C5. Now that if we want that range to be flexible. The only thing we know about the data range is all the data surround cell "C3". So, I tried to use
SourceData := Range("C3").currentRegion.address
 to replace
SourceData:= "Sheet1!R2C2:R11C5"

But it does not seem to work. Anyone knows a better way to do it?

Thanks,
RDB

The following is the sample code:


Sub Macro2()
Dim pc As PivotCache
Dim pt As PivotTable
    Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C2:R11C5")
    Set pt = pc.CreatePivotTable(TableDestination:=Range("B5"), _
        TableName:="PivotTable1")
        With pt
        .PivotFields("month").Orientation = xlColumnField
        .PivotFields("region").Orientation = xlRowField
        .PivotFields("sales").Orientation = xlDataField
        End With
End Sub
0
ResourcefulDB
Asked:
ResourcefulDB
2 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
CurrentRegion should work as long as there are no values in any adjacent columns or rows. If there are then insert new empty columns and rows and then hide them.

Kevin
0
 
jeveristCommented:
Hi RDB,

Kevin's right about CurrentRegion but to use it as your PivotTable SourceData you need to do something like this:

Set ws = Worksheets("Sheet1")

Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ws.Range("C3").CurrentRegion)

or this:

Set ws = Worksheets("Sheet1")

Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    ws.Name & "!" & ws.Range("C3").CurrentRegion.Address(ReferenceStyle:=xlR1C1))

Jim
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now