Selecting all data to create a pivot table

[sobeitjedi]

Hi.

I have created a macro to create a pivot table. It all works apart from one thing. The sheet I created the macro with has two columns (in a & b) and 426 rows. How to I change my macro so that it will use all the rows that are populated in columns a & b as it won't always be 426 rows (could be more, could be less).

Here's my code:

Sub Macro5() ' ' Macro5 Macro ' ' Range("A1:B426").Select Range("B9").Activate ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R426C2", Version:=6).CreatePivotTable TableDestination:= _ "Sheet1!R1C4", TableName:="PivotTable7", DefaultVersion:=6 Sheets("Sheet1").Select Cells(1, 4).Select With ActiveSheet.PivotTables("PivotTable7") .ColumnGrand = True .HasAutoFormat = True .DisplayErrorString = False .DisplayNullString = True .EnableDrilldown = True .ErrorString = "" .MergeLabels = False .NullString = "" .PageFieldOrder = 2 .PageFieldWrapCount = 0 .PreserveFormatting = True .RowGrand = True .SaveData = True .PrintTitles = False .RepeatItemsOnEachPrintedPage = True .TotalsAnnotation = False .CompactRowIndent = 1 .InGridDropZones = False .DisplayFieldCaptions = True .DisplayMemberPropertyTooltips = False .DisplayContextTooltips = True .ShowDrillIndicators = True .PrintDrillIndicators = False .AllowMultipleFilters = False .SortUsingCustomLists = True .FieldListSortAscending = False .ShowValuesRow = False .CalculatedMembersInFilters = False .RowAxisLayout xlCompactRow End With With ActiveSheet.PivotTables("PivotTable7").PivotCache .RefreshOnFileOpen = False .MissingItemsLimit = xlMissingItemsDefault End With ActiveSheet.PivotTables("PivotTable7").RepeatAllLabels xlRepeatLabels ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _ "PivotTable7").PivotFields("Description#1"), "Count of Description#1", xlCount With ActiveSheet.PivotTables("PivotTable7").PivotFields("Description#1") .Orientation = xlColumnField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable7").PivotFields("Date") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable7").PivotFields("Date").AutoGroup ActiveSheet.PivotTables("PivotTable7").PivotFields("Months").Orientation = _ xlHidden Range("D13").Select End Sub

 

[StuLux]

Replace the line Range("A1:B426").Select
with this code:

Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:B" & LastRow).Select

This will count the number of rows being used in Column A and then select that number of rows in both columns A and B

 

[sobeitjedi]

Replace the line Range("A1:B426").Select
with this code:

Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:B" & LastRow).Select

This will count the number of rows being used in Column A and then select that number of rows in both columns A and B

Thanks, but what about this part?..
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R426C2", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R1C4", TableName:="PivotTable7", DefaultVersion:=6

 

[sobeitjedi]

This part specifically ... "Sheet1!R1C1:R426C2"

How can I feed into the pivot table creation, all the rows to use, when the rows will be an unknown number?

 

[StuLux]

I would suggest either changing the data area to a table (thereby Excel will automatically include any new rows added) or naming the data range using 'Dynamic Named Range' (look this up if you are not familiar with it). If you then re-record your macro but use the named range instead of a reference to row/column it will include additional rows as they are added.
It might help if you could explain why you feel the need to record a macro to create a pivot table as this may be unnecessary i.e. why can;t the pivot table simply exist at all times and then just refreshed when new data is added?