Excel VBA - Array & Range - Help Required

iGavC

New Member
Joined
Aug 1, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

You could add a variable

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,081
Messages
5,484,556
Members
407,454
Latest member
moomen1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top