Problem to Create a Pivot Table in VBA - File with more than 100k rows!

[Roque]

Hello!
I have created the code below in order to have a pivot table automatically, but something is not working properly.

Sub DBPivotTables()


Dim lrowdb As Long
lrowdb = ActiveWorkbook.Sheets("db_tr").Cells(Rows.Count, 1).End(xlUp).Row

Dim rangesodt As Range
Set rangesodt = ActiveWorkbook.Sheets("db_tr").Range("a1:h" & lrowdb)
Dim rangegr As Range
Set rangegr = ActiveWorkbook.Sheets("db_tr").Range("a1:e" & lrowdb)
Dim rangesch As Range
Set rangesch = ActiveWorkbook.Sheets("db_tr").Range("a1:g" & lrowdb)

Dim sodtpt As PivotTable
Dim sodtcache As PivotCache
Dim sodtfields As PivotField
Dim sodtitems As PivotItem


On Error Resume Next
Sheets("sodt_tr").Select
ActiveSheet.PivotTables("sodt").TableRange2.Clear


Sheets("db_tr").Select
Set sodtcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, rangesodt)


Sheets("sodt_tr").Select
Set sodtpt = ActiveSheet.PivotTables.Add(sodtcache, Range("a2"), "sodt")


With sodtpt
    .PivotFields("pn").Orientation = xlRowField
    .PivotFields("so dt").Orientation = xlDataField


End With


End Sub

I suppose it is a problem related to high number of rows (up to 100k) because when I change the range of the cache to 200 rows it works!

Sorry, I forget to mention! I am working with Excel 2010!

 

[Jerry Sullivan]

Hi Roque,

The limit occurs when trying to pass a Range object with greater than 65536 rows (max number of rows for xl2003), even if you are using xl2007 or later.

The SourceData parameter, which is a Variant type, handles either Range objects or Strings representing Range Address references below this limit.

For Ranges greater than 65536 rows, it appears to only work with Address References and not Range Objects.

A workaround for your code would be to convert the Range object into an Address reference.

Set sodtcache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, SourceData:= rangesodt.Address(External:=True))

 

[Roque]

Thanks for detailed explanation. It worked perfectly!

 

[Roque]

Have another question that just popped... How do I change the function on the pivot table to xlmin? It assumes xlcount as standard. Further it doesn't allow to format as date dd/mm/yy. In red below...

Sub DBPivotTables()

Dim sodtpt As PivotTable
Dim sodtcache As PivotCache
Dim sodtfields As PivotField
Dim sodtitems As PivotItem


On Error Resume Next
Sheets("sodt_tr").Select
ActiveSheet.PivotTables("sodt").TableRange2.Clear


Sheets("db_tr").Select
Set sodtcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _
rangesodt.Address(External:=True)) 'use address when more than 65536 rows


Sheets("sodt_tr").Select
Set sodtpt = ActiveSheet.PivotTables.Add(sodtcache, Range("a2"), "sodt")


With sodtpt    .PivotFields("pn").Orientation = xlRowField
    .PivotFields("so dt").Orientation = xlDataField
End With


End Sub

 

[Andrew Poulsom]

If Excel is using Count by default it's likely that you have some blanks or non-serial dates in the field. By non-serial I mean that the dates are text rather than true dates which are numbers.

 

[Roque]

You are right! however it worked when I amended the code:

sodtpt.PivotFields("pn").Orientation = xlRowField
With sodtpt.PivotFields("so dt")
    .Orientation = xlDataField
    .Function = xlMin
    .NumberFormat = "dd/mm/yy;@"
End With

Now, how do I change the pivot table parameters to consider 0 (zero) when finding blank cells in the data range?
I tried as below, but it is not working:

Sub DBPivotTables()


Dim lrowdb As Long
lrowdb = ActiveWorkbook.Sheets("db_tr").Cells(Rows.Count, 1).End(xlUp).Row


Dim rangesodt As Range, rangegr As Range, rangesch As Range
Set rangesodt = ActiveWorkbook.Sheets("db_tr").Range("a1:h" & lrowdb)
Set rangegr = ActiveWorkbook.Sheets("db_tr").Range("a1:e" & lrowdb)
Set rangesch = ActiveWorkbook.Sheets("db_tr").Range("a1:g" & lrowdb)




Dim wdb As Worksheet, wsodt As Worksheet, wgr As Worksheet, wsch As Worksheet
Set wdb = ActiveWorkbook.Sheets("db_tr")
Set wsodt = ActiveWorkbook.Sheets("sodt_tr")
Set wgr = ActiveWorkbook.Sheets("gr_tr")
Set wsch = ActiveWorkbook.Sheets("sch_tr")


Dim sodtpt As PivotTable
Dim sodtcache As PivotCache
Dim sodtfields As PivotField
Dim sodtitems As PivotItem


On Error Resume Next
wsodt.Select
ActiveSheet.PivotTables("sodt").TableRange2.Clear


wdb.Select
Set sodtcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, _
rangesodt.Address(external:=True)) 'use address when more than 65536 rows


wsodt.Select
Set sodtpt = ActiveSheet.PivotTables.Add(sodtcache, Range("a2"), "sodt")


sodtpt.PivotFields("pn").Orientation = xlRowField


With sodtpt.PivotFields("so dt")
    .Orientation = xlDataField
    .Function = xlMin
    .NumberFormat = "dd/mm/yy;@"
End With


Dim grpt As PivotTable
Dim grcache As PivotCache
Dim grfields As PivotField
Dim gritems As PivotItem


On Error Resume Next
wgr.Select
ActiveSheet.PivotTables("gr").TableRange2.Clear


Set grcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, rangegr.Address(external:=True))


wgr.Select
Set grpt = ActiveSheet.PivotTables.Add(grcache, Range("a2"), "gr")


With grpt
    .PivotFields("pn").Orientation = xlRowField
    .PivotFields("month").Orientation = xlColumnField
End With
    
With grpt.PivotFields("net req")
    .Orientation = xlDataField
   .NullString = "0" 'not working :(
End With


End Sub

 

[Andrew Poulsom]

Try:

.Parent.NullString = "0"

 

[Roque]

Worked perfectly! Thanks!