Excel VBA - Array & Range - Help Required

[iGavC]

Hi,

I was wondering if anyone could assist.
I'm currently asking my VBA Code to create a multiple consolidated range with a data set I have. It's currently reference within a table which I've named: "Client_MI"

Right now, the data set it going to the 5000th row down. Can I point my range to just simply go to the end of the data source?:

Please see below:
Sub Create_Data_Table()

' Removes alerts

Application.DisplayAlerts = False

' Runs Multiple Consolidated Ranges for Data Table

ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("'Client - MI Report'!R2C66:R5000C188"), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(2, 1)
ActiveSheet.Cells(2, 1).Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Count of Value").Position = 1

Application.DisplayAlerts = True

End Sub


Appreciate any assistance on this.

Thank you
Kind regards

 

[James006]

Hi,

You could add a variable

Dim last As Long
last = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' and then amend your instruction
[I]Array("'Client - MI Report'![COLOR=#ff0000]BN2:GF" & last[/COLOR])[/I]

Hope this will help