Dynamic Range for Pivot Table

[Guest]

I am reading information in from a SQL Server Database via OLE DB, putting
results into a worksheet which is then used to populate a Pivot Table. Since
the number of records returned can vary, I set the rows on the worksheet to
an arbitrary 25000. How do I get it so that once I read in the ADO
recordset, via
Set xlData = Sheets("Sheet2").Range("A2:AC25000")
xlData.CopyFromRecordset aRS
How do I/can I remove all blank rows?

Thanks

 

[Guest]

Hi Rob,

You can do this one of two ways:

If your column is remaining the same, do the following:

Dim lastrow As Long
lastrow = (ActiveWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row)
Set xlData = Sheets("Sheet2").Range("A2:AC" & lastrow)
xlData.CopyFromRecordset aRS

- OR -

If your data changes in size for Column and rows, use this:

ActiveWorkbook.Names.Add Name:="PivotRange",
RefersToR1C1:="=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1),COUNTA(Sheet2!R1))"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="PivotRange").CreatePivotTable TableDestination:="",
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True

Hope this helps!

Jeff