code to delete all rows beneath and columns right of pivot table

[klysell]

Hi,

My macro parses codes on my summary sheet into separate pivot tables
contained on separate worksheets thereby using these individual codes as page
fields for these pivot tables. Because I have too much data, I had to create
a forumula driven variance column lying outside of the pivot table (else the
code that creates the pivot table crashes). However, now my print macros
don't work because for some reason, the print preview function reads all the
variance formulas (although blank) going to down to, say, row 2000. Instead
of nicely fitting the pivot table onto one, two or three pages, I have 65+
blank pages due to the formulas running down past my pivot table (these
variance formulas show a blank i.e. "" in cells below my pivot tables).

Therefore, I either: 1) need to create a print area for just the pivot
table; or 2) delete all rows beneath the macro-generated pivot table
(starting row not known in advance).

Any help would be appreciate.

Cheers,

Kent Lysell
IBM Consultant
Ottawa, Ontario

 

[Bernie Deitrick]

Kent,

1)
To set the print area to just the pivot table, use code like

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PageSetup.PrintArea = Selection.Address

2)

Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Set myR = Selection

Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1, Columns.Count)).EntireColumn.Delete


If the sheet isn't the activesheet, use Worksheets("Sheetname").Activate first, in either example.
And, of course, you will need to change the name of the pivottable. If each sheet has only one, then

ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel, True

should work.

HTH,
Bernie
MS Excel MVP

 

[klysell]

Thanks, that's great Bernie. One more thing: I need to select all leading
rows above the pivot table (rows 1 to 5) as well as the pivot table (starting
on row 12) since the leading rows contain formula-driven titles. I don't know
the name the sheet beforehand, so I'll have to create a name range on the
master pivot table from which the macro copies to create the new pivot
table(s). - K.

 

[klysell]

Bernie,

I tried to second method to delete rows below my macro-generate pivot table
and got an error message "Method 'Range' of Object '_Worksheet' failed". Here
is the code that you gave me in the context of my code:

Sheets("PIV_SOF").Visible = True
Application.Goto "SOF_BACK_TO_SUMMARY"
Worksheets("PIV_SOF").Copy _
After:=Worksheets(Worksheets.Count)
Sheets("PIV_SOF").Visible = False

Set Sh = ActiveSheet
Sh.Name = Target & "-Source of Funds"
Sh.Tab.ColorIndex = 33
For Each pt In Sh.PivotTables
With pt
With .PivotFields("RC")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value

Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
Set myR = Selection

Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1,
Columns.Count)).EntireColumn.Delete

Call Formatting_SOF
'
'
'
'
Any suggestions?
Thanks.