VBA Excel: Last Row/Column in Code for Pivot Table
Lena
-
Lena -
Lena -
Hello,
I am working on Excel 2013, and I wanted to automate the creation of a pivot table (T.C.D.) using VBA. To do this, I relied on the macro recorder which produced an interesting code for me:
I am reaching out to you because I would like to know how to adapt this code to source data that may have many more rows and columns than the 13 and 6 in this example without having to specify it each time. In other words, is there a way for it to automatically find the last rows and columns? Does anyone have a lead?
Thank you in advance!
I am working on Excel 2013, and I wanted to automate the creation of a pivot table (T.C.D.) using VBA. To do this, I relied on the macro recorder which produced an interesting code for me:
Sub Macro2()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R13C6", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:="TT!R1C1", TableName:="PivotTable1", DefaultVersion:=5
Sheets("TT").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("City")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
.Orientation = xlRowField
.Position = 2
End With
End Sub
I am reaching out to you because I would like to know how to adapt this code to source data that may have many more rows and columns than the 13 and 6 in this example without having to specify it each time. In other words, is there a way for it to automatically find the last rows and columns? Does anyone have a lead?
Thank you in advance!
1 answer
-
Hello,
an example:
Sub Creation_TCD() Call DeletePivotTable adr = Sheets("feuil1").UsedRange.Address ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Feuil1!" & Sheets("feuil1").UsedRange.Address, Version:=xlPivotTableVersion15).CreatePivotTable _ TableDestination:="TT!R1C1", TableName:="PivotTable1", DefaultVersion:=5 Sheets("TT").Select Cells(1, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("City") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name") .Orientation = xlRowField .Position = 2 End With End Sub 'https://www.developpez.net/forums/d1423895/logiciels/microsoft-office/excel/vba-supprimer-tcd/ 'Philippe Tulliez 'Writer Sub DeletePivotTable() Dim sht As Worksheet, pvt As PivotTable, Response As Byte, msgResponse As String Set sht = ThisWorkbook.Worksheets("TT") For Each pvt In sht.PivotTables With pvt msgResponse = msgResponse & .Name & vbCrLf & "Cells: " & .TableRange2.Address Response = MsgBox(msgResponse, vbYesNo + vbExclamation, "Delete Pivot Table") If Response = vbYes Then .TableRange2.Delete Shift:=xlToLeft ' or Shift:=xlUp End With Next End Sub
To adapt
