1

UPDATED

I need to do a row count of the rows in the pivot table between the "header" row of the table and the "grand total" row of the table and then copy the value that the count finds, to a summary sheet.

Header Row:

FirstRow

Last Row:

LastRow

So I pretty much need to do a count from row 4 - row 133 (which in this case is a total row count of 130) of the pivot table and paste that value in cell B23 on the summary sheet.

Current Pivot Table Layout:

CurrentPivotTableLayout

I need to integrate the count into the code I already have, which is below:

Option Explicit Sub FilterPivotTable() Dim rLastCell As Range Dim PvtTbl As PivotTable Dim ws1 As Worksheet, ws2 As Worksheet Dim LastRow1 As Long Set ws1 = ActiveWorkbook.Sheets("PivotTable") Set ws2 = ActiveWorkbook.Sheets("Summary") LastRow1 = ws1.Cells(Rows.count, 1).End(xlUp).Row 'Total - This inserts the word Total in cell B22 and pastes the total value 'found in the grand total line of the of the pivot table, in the summary sheet 'in line C22 Application.ScreenUpdating = False ws1.PivotTables("P1").ManualUpdate = True ws1.PivotTables("P1").ManualUpdate = False Application.ScreenUpdating = True With ws1.PivotTables(1).TableRange1 Set rLastCell = .Cells(.Rows.count, .Columns.count) Set PvtTbl = Worksheets("PivotTable").PivotTables("P1") rLastCell.Copy With ws2 ws2.Cells(LastRow1 + 22, 3).PasteSpecial xlPasteValues .Range("$B$22").Value = "Total" End With End With 'Microsoft Windows - This filters the table by any vulnerabilities that have 'the words Microsoft Windows in their description, then it inserts the words 'Microsoft Windows in cell B2 and the grand total count of this filter 'in cell C2 Application.ScreenUpdating = False ws1.PivotTables("P1").ManualUpdate = True ws1.PivotTables("P1").PivotFields(" Vulnerability Name").ClearAllFilters ws1.PivotTables("P1").PivotFields(" Vulnerability Name").PivotFilters. _ Add Type:=xlCaptionContains, Value1:="Microsoft Windows" ws1.PivotTables("P1").ManualUpdate = False Application.ScreenUpdating = True With ws1.PivotTables(1).TableRange1 Set rLastCell = .Cells(.Rows.count, .Columns.count) Set PvtTbl = Worksheets("PivotTable").PivotTables("P1") rLastCell.Copy With ws2 .Cells(LastRow1 + 2, 3).PasteSpecial xlPasteValues .Range("$B$2").Value = "Microsoft Windows" End With End With ws1.PivotTables("P1").PivotFields(" Vulnerability Name").ClearAllFilters 'Microsoft Office- This filters the table by any vulnerabilities that have 'the words Microsoft Office in their description, then it inserts the words 'Microsoft Windows in cell B3 and the grand total count of this filter 'in cell C3 Application.ScreenUpdating = False ws1.PivotTables("P1").ManualUpdate = True ws1.PivotTables("P1").PivotFields(" Vulnerability Name").PivotFilters. _ Add Type:=xlCaptionContains, Value1:="Microsoft Office" ws1.PivotTables("P1").ManualUpdate = False Application.ScreenUpdating = True With ws1.PivotTables(1).TableRange1 Set rLastCell = .Cells(.Rows.count, .Columns.count) Set PvtTbl = Worksheets("PivotTable").PivotTables("P1") rLastCell.Copy With ws2 ws2.Cells(LastRow1 + 3, 3).PasteSpecial xlPasteValues .Range("$B$3").Value = "Microsoft Office" End With End With ws1.PivotTables("P1").PivotFields(" Vulnerability Name").ClearAllFilters End Sub

So far I have this code to do the count:

lRowCount = ActiveSheet.PivotTables("P1").TableRange1.Rows.Count

But not sure where to integrate this and how copy the lRowCount value.

This row count needs to be done in each With block of code. So when the filter is done on vulnerability name for Microsoft Office, I need a row count done on the filtered data too.

Improve this question
12
  • I am confused. You are changing the pivotfield rather than filtering what is visible within a given field, no? Commented Apr 6, 2018 at 12:09
  • Damn, now I am confused too.. The intentions was that there's effectively two filters being applied - 1) Filter on the first parameter and then 2) Filter within the "filtered" results of the firs parameter. I updated the code with an example Commented Apr 6, 2018 at 12:16
  • Are these page fields? Can you show your pivottable layout? And what did your macro recorder record if you tried performing a couple óf these operations? Commented Apr 6, 2018 at 12:38
  • 1
    Could you post a screenshot of your pivot table and data - slimmed down if poss just to give an idea? Commented Apr 6, 2018 at 12:42
  • I have updated the original post significantly @SJR - I hope it makes more sense now? I unfortunately cannot post screenshots of the actual data, however I am hoping what I have provided is enough to allow you to help me. Commented Apr 8, 2018 at 10:21

2 Answers 2

Reset to default
3

A simply count of the pivottable data area rows is

DataBodyRange.Rows.Count

Example:

Worksheets("PivotTable").ListObjects(1).DataBodyRange.Rows.count

More verbose:

Option Explicit Sub test() Dim rowCount As Long rowCount = Worksheets("PivotTable").ListObjects(1).DataBodyRange.Rows.count Worksheets("Summary").Range("B23") = rowCount End Sub

In your case can shorthand to:

PvtTbl.DataBodyRange.Rows.count
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

I think this should get you going.

Sub PivotTable(ByVal ParamValue As String) Dim rLastCell As Range Dim PvtTbl As PivotTable Dim ws1 As Worksheet, ws2 As Worksheet Dim LastRow1 As Long 'Your lRowCount variable Dim lRowCount As Long Set ws1 = ActiveWorkbook.Sheets("PivotTable") Set ws2 = ActiveWorkbook.Sheets("Summary") LastRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row Application.ScreenUpdating = False With ws1.PivotTables(1) .ManualUpdate = False Set rLastCell = .TableRange1.Cells(.Rows.Count, .Columns.Count) Set PvtTbl = Worksheets("PivotTable").PivotTables("P1") '??? ' 'Get the row count from your pivot table. lRowCount = .Rows.Count With ws2 'Performance enhancement, as there is no need for copy/past. .Cells(LastRow1 + 22, 3) = rLastCell.Value .Range("$B$22").Value = "Total" ' 'Arbitrary place to copy to. Adjust to your need. .Cells(LastRow1 + 23, 3) = lRowCount .Range("$B$23").Value = "Rowcount P1" End With End With With ws1.PivotTables("P1") .ManualUpdate = True .PivotTables("P1").PivotFields(ParamValue).ClearAllFilters .PivotTables("P1").PivotFields(ParamValue).PivotFilters _ .Add Type:=xlCaptionContains, Value1:=ParamValue .PivotTables("P1").ManualUpdate = False End With With ws1.PivotTables(1).TableRange1 Set rLastCell = .Cells(.Rows.Count, .Columns.Count) Set PvtTbl = Worksheets("PivotTable").PivotTables("P1") '??? With ws2 .Cells(LastRow1 + 2, 3).Value = rLastCell.Value .Range("$B$2").Value = "Microsoft Windows" End With End With Application.ScreenUpdating = True End Sub ' 'Usage: ' call PivotTable("Param1") ' call PivotTable("Param2") ' etc.
Improve this answer

4 Comments

Thank you for your reply @C. van Dorsten. I copied your code and tried to test it, but the Macro wasn't showing in the list of available Macro's. However, I have edited the post to a specific question which has been answered above. Thank you again :)
Ofcourse it won't, because it takes a parameter. You should have renamed it or whatever. I just used your code to give you an example you could work with. So why the down vote?
I am not the one who down voted. Please don't assume because I didn't get the desired result with your help that I immediately down voted you.
Ok. Sorry for my remark. Had a bad day that day at work, but shouldn’t have taken it out on you. Again sorry.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.