View original (French)

VBA Pivot Table/Range to Last Filled Row

Jerome -  
 Jerome -
Hello,

I am reaching out for your help because I am struggling with a Pivot Table in VBA. I am not very skilled, so I create all my macros using the recorder, which comes with its share of errors. As a result, I go back over them in VBA, but as often happens, I hit a wall.

Here is my issue: I created a Pivot Table (with the recorder) in my sheet named "Suivi". I select cell A1 for the start of my Pivot Table, and I go to a sheet named "IEP" to fetch my range. Currently, the macro records the range from cell A1 to S8320 (the last non-empty row). However, in the following weeks, my range will vary and may have 10,000 or even 15,000 rows. Therefore, I would like to modify the code below simply to get the last non-empty row instead of S8320 corresponding to the execution of my macro via the recorder.

Sheets("Suivi").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"IEP!R1C1:R8320C19", Version:=6).CreatePivotTable TableDestination:= _
"Suivi!R1C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Suivi").Select
Cells(1, 1).Select

So, I made attempts like below, but it doesn't work and gives me the runtime error 1004 (The reference is not valid). Could you please help me?

Sheets("Suivi").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select
'Variable last filled row from the IEP sheet in column A
LIEP = Sheets("IEP").Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"IEP!$A$1:$S$" & LIEP, Version:=6).CreatePivotTable TableDestination:= _
"Suivi!R1C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Suivi").Select
Cells(1, 1).Select

Thank you all for your help,

2 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello Jerome

    You were almost there! The correct notation is not "IEP!$A$1:$S$ & LIEP" but "IEP!$A$1:$S$" & LIEP

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. Jerome
       
      Hello Via,

      Honestly and sincerely, I want to say a huge thank you for your help. I'm not great at this and I hope to improve ;-) but in the meantime, it's thanks to people like you that I'm able to make progress.

      I’d like to take this opportunity to ask you a small question, if you don’t mind, and for my understanding: when I create variables like this:

      'Variable for the last filled row in the IEP tab of column A
      LIEP = Sheets("IEP").Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row

      'Variable to determine the last filled row of column M
      ligne = Sheets("IEP S-1").Columns(13).Find("*", , , , xlByColumns, xlPrevious).Row

      'Variable derligne = Sends the formula down to the last filled row of column D
      derligne = Range("D" & Rows.Count).End(xlUp).Row

      I didn't create a DIM corresponding to (LIEP, ligne, and derligne) by mistake at the beginning, but it works perfectly fine without it? Is it logical because I don't understand the purpose of DIM and I feel like they're not necessary? Is it normal that it works well without them?

      Here are some examples of codes where I use it

      'Send "Yes" down to the last row
      Range("R2").AutoFill Destination:=Range("R2:R" & derligne)
      ActiveCell.Formula = "=IFERROR(VLOOKUP(N2,'IEP S-1'!$M$2:$R" & ligne & ",6,0),""No"")"
      Range("S2").Select

      'Send the VLOOKUP formula down to the last row
      Selection.AutoFill Destination:=Range("S2:S" & derligne)

      'Select the range of the pivot table down to the last row
      ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      "IEP!$A$1:$S$" & LIEP, Version:=6).CreatePivotTable TableDestination:= _
      "Suivi!R1C1", TableName:="PivotTable1", DefaultVersion:=6
      Sheets("Suivi").Select

      Thank you for your help :-)
      0
  2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Re,

    Yes, it works without declaration.
    By default, without DIM, all variables will be of type Variant (which can take 22 bytes of memory, while a variable of type Integer only takes 2 bytes, see: https://docs.microsoft.com/fr-fr/office/vba/language/reference/user-interface-help/data-type-summary, here is the first usefulness of declaration
    The second usefulness is to help keep track, see: https://www.excel-pratique.com/fr/vba/variables

    Regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1
    1. Jerome
       
      Hello again Via,

      Thank you very much for this very clear explanation. Indeed, I understand much better its interest now. Since my macro is already very heavy, I definitely have every reason to replace my variable with an integer.

      Looking forward to it and thank you again for everything.

      Best regards
      0