Starting point of a Pivot Table and best way to copy the data from it and delete blank rows

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I've got two quick questions:

1) Is there a way to check the starting cell of a Pivot table (from a file you've inherited)?

2) What's the best way to copy the data and delete the blank rows from a Pivot Table?

I've written this, but I was wondering if there's a better way?

Code:
‘copy data from the Pivot Sheet (assuming it starts in cell A3)
PivotSheet.Activate
Range("A3").CurrentRegion.Copy
Paste it into a new sheet
NewSheet.Activate
Range("A1").PasteSpecial xlPasteValues
'delete the blank rows (the blank rows in this Pivot data appear in columns B:D)
 Range("B:D").Select
 Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,118,792
Messages
5,574,322
Members
412,587
Latest member
Krucial155
Top