Hey,

I just want to know if there is any limit set to number of rows or columns (or maybe cells) that can be contained within a range object?

I need to process the data that contains at least 30k records on the daily basis with more than 200 columns. I can easily reduce the number of columns to 20 without any loss of data, but the records need to be consolidated. So, I create a dynamic range to be used with the pivot which is then further processed to reduce the number of records to no more than 2,000. However, the function fails to create the range when number of records crosses 50k. The sample code is shared as below:


With ws
    lngRowCount = .Cells(.Rows.Count, 1).End(xlUp).Row      ' Getting record count in the table
    lngColCount = .Cells(1, .Columns.Count).End(xlToLeft).Column   ' Capturing the field count
    
    ' Defining the data source for the pivot table, works like a charm when lngRowCount <=50000
    Set rngDataSource = .Range(.Cells(1, 1), .Cells(lngRowCount, lngColCount))
End With

' Creating a new worksheet for processing pivot table
Set CreateNewPivotTable = wbk.Worksheets.Add
CreateNewPivotTable.Name = "PivotDataWS"

' Creating Pivot Cache
Set pcAgentData = wbk.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngDataSource)
Rds,