Issue with Pivot Destination and dynamic range

[allExcell]

Hi All,

I need your help on the below code. The code I've written to create a pivot is working fine so far, but I need some help to modify it a bit, and this is where I'm stuck now.

Option Explicit
Sub testme1()

Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet
Dim PT As PivotTable
Dim Pf As PivotField
Dim vRowFields() As Variant
Dim vDataFields() As Variant
vRowFields = Array("A", "B", "C", "D")
 
Set wks = Worksheets("Sheet1")

With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2 'skipping columns A
LastCol = 286
 

ReDim DataFieldArray(26 To 286)
For iCol = 26 To 286
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol

.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
[B]SourceData:=wks.Range("Contents")).CreatePivotTable _
TableDestination:=Sheets("Pivot").Range("A5"), _
[/B]TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
End With

With Sheets("Pivot")
.Activate
Range("A5").Select

.PivotTables("PivotTable1").AddFields RowFields:=vRowFields
 
Dim i As Integer
    Dim iFieldMax As Integer
     On Error Resume Next
     ' Find the number of PivotFields
    iFieldMax = ActiveSheet.PivotTables("PivotTable1").PivotFields.Count
     
     ' Loop through the fields in the Pivot
    For i = 1 To iFieldMax
         
        With ActiveSheet.PivotTables("PivotTable1").PivotFields(i)
             
             'Set subtotal calculation to nothing
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
             
        End With
         
    Next i
For iCol = 26 To 286
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Function = xlSum
End With
Next iCol
With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(1).ColumnGrand = False
 
End With
End Sub

Here I want to make the pivot range dynamic. I'm not able to make the source data dynamic. Please see below, this is what I'm using at present.

[B]SourceData:=wks.Range("Contents")).CreatePivotTable [/B]

Again, the Pivot destination has to be hard coded with something like

[B]TableDestination:=Sheets("Pivot").Range("A5"), [/B]

. Unfortunately, this one is throwing an error, saying "Run-time error '1004'. Application-defined or object-defined error." while selecting below lines of code.

.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=wks.Range("Contents")).CreatePivotTable _
TableDestination:=Sheets("Pivot").Range("A5"), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

Any help would be appreciated.

Thanks in advance.

 

[Jerry Sullivan]

Define Name by using "Name Manager" to your Pivot Table Source Data and write following formula in "Refers to" selection bar for dynamic range:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

Hi vikas9385,

While that is one way to make a Dynamic Named Range (DNR), if I'm understanding this allExcel doesn't want the data source to be the entire data range, only part of it. My questions are attempting to clarify how the number of columns to be included could be defined.

One could test each cell in the header row until finding a formula or blank using VBA,...but the bigger picture I'm trying to understand is does this setup really need to be so complicated?

 

[Jerry Sullivan]

allExcell,

I might have misinterpreted your earlier description as meaning that the number of columns in the data source will vary.

From your last post (#10) is it correct to say that the data source will always go from Column B to Col Z, beginning with headers in Row 11 with a variable number of Rows?

 

[vikas9385]

Excel Pivot Table -- Dynamic Data Source

chk out dis link...hope dis will help u alot.

 

[allExcell]

Hi Vikas,

I've seen this post earlier. The main problem with this "formula" is that it will take all the blank cells where ever any formula is written. I want something that should pick-up only the cells containing value and not the one which is appearing blank but has formula hidden in them.

Thanks for your help.

 

[allExcell]

Hi Jerry,

Let me put it this way. MY data source is having more than 286 columns and 1000 rows as of now. Out of which, B to Z are the columns having column headers as Values, as for eg. "A", "B",..."Z". Starting from "AA" to let's sa "KA" are the columns which are actually coming in the DataField as "Summary of dd-mm-yyyy". Here the column headers eg. "dd-mm-yyyy" is generated from a formula, so these are not just values, it has formula too. Out of B to Z streatch, I've selected few columns in the RowField and from AA till KA will be there in the DataField as multiple DataField items.


So the Data Source would be something starting from B11 till KA1000 (again, this can increase in near future), where it has to consider cells with Values. My present code picks up all the rows, which are not having any value as they have formula in them. This appears in Pivot as (Blank).


So, my purpose is to pickup only till the last row it has value, that means if it's appearing as Blank, even though it has some formula in it, should not be picked. Moreover, I've seen running a code like this with let's say 600 rows of data is taking nearly 10 mins, which is too bad for me ! And funniest part is , I'm not able to fine tune it further.

So, the number of Row is variable but number of column is constant.

Hope this helps. Please let me know if you have any more doubt.

Thanks for your precious time.

 

[Jerry Sullivan]

It's clear the top left cell of the data source range is B11.

For the bottom right cell is it correct that its column will be the last column of data?

I'm understanding now that the you want to use the boundary of "values and formulas" to find the last Row (not Column) of the data source.

When you use the term "value" do you mean:
1. A cell with a constant value; or
2. A cell with a formula that displays something other than a blank ""?

When you use the term "formula" do you mean:
1. A cell with any formula; or
2. A cell with a formula that displays a blank ""?

 

[allExcell]

It's clear the top left cell of the data source range is B11.

Yes, you are right.

For the bottom right cell is it correct that its column will be the last column of data?

Yes, you are right at the point. Column for the bottom right cell would be the last column of the data, say 286. Though, there are some more columns in the base sheet, but I'm considering only till (say) 286th column for the Pivot creation.

I'm understanding now that the you want to use the boundary of "values and formulas" to find the last Row (not Column) of the data source.

Yes, you are absolutely right.Last column is already been decided. Here, I need to find out which one is the last row to be considered. As blank rows are not actually blanks, they have formula in them. So, when defining the range for the pivot, those apparently blank looking cells should not be picked up, insted, we sould consider only those, which has value in them.

When you use the term "value" do you mean:
1. A cell with a constant value; or
2. A cell with a formula that displays something other than a blank ""?

This could be both 1. & 2.

When you use the term "formula" do you mean:
1. A cell with any formula; or
2. A cell with a formula that displays a blank ""?

Here I mean, a cell with a formula that displays a blank.

 

[allExcell]

Hi Jerry,

I just want to add few more points here. With the present code, if I try hardcoding the range let's say for 700 rows, the codeis taking nearly 10 mins to run to completion. If, it's for 300 rows, it's taking more than 3 mins. For a row size of 100, it's taking almost a min.
As for example, if there are only 50 rows of data, but my range is hardcoded to 700 rows, it's taking same time as nearly 10 mins. I hope, thsi is because of teh formula in those blank cells which is making teh code run slow.This is absolutely not desirable. I need your thoughts on this as well.

 

[allExcell]

Hi All,

Is this doable? else, can anyone please let me know if there is any other way I can try?

 

[Jerry Sullivan]

Hi allExcell,

Here's some code that should work for your range with a variable number of rows.

Sub MakePivot()
    Const HeaderRow As Long = 11
    Const FirstCol As Long = 2  'skipping columns A
    Const LastCol As Long = 286 'fixed last column
    Dim myRng As Range
    Dim LastRow As Long

    
    On Error GoTo CleanUp

    
    With Worksheets("Sheet1")
        LastRow = .Columns(FirstCol).Find("*", After:=.Cells(1, FirstCol), LookIn:=xlValues, _
            SearchOrder:=xlRows, SearchDirection:=xlPrevious).Row

        
        Set myRng = .Range(.Cells(HeaderRow, FirstCol), .Cells(LastRow, LastCol))

    
        Application.Calculation = xlCalculationManual

        
        .Parent.PivotCaches.Add(SourceType:=xlDatabase, _
            SourceData:=myRng).CreatePivotTable _
            TableDestination:=Sheets("Pivot").Range("A5"), _
            TableName:="PivotTable1", _
            DefaultVersion:=xlPivotTableVersion10
    End With

CleanUp:
    Application.Calculation = xlCalculationAutomatic
End Sub

Temporarily setting Calculation to Manual might help with the speed.
There is only so much you can do if the person you are helping doesn't want to change their workbook to eliminate the costly formulas.