Coded to delete extra rows after pivot table
 

I tried to use some code given to me on this site to delete rows after
macro-generated pivot tables, but this did not work. I received an error
message, "Method 'Range' of Object '_Worksheet' failed". The code that I
received was as follows:
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
'
'
'
This code sandwiched in with other code as shown below:
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 in advance.



--
Kent Lysell
IBM Consultant
Ottawa, Ontario

Coded to delete extra rows after pivot table
 

Here is some code that I use... Just call CompactSheet. If you do not supply
a worksheet then it works on the active sheet...

Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


"klysell" wrote:

I tried to use some code given to me on this site to delete rows after
macro-generated pivot tables, but this did not work. I received an error
message, "Method 'Range' of Object '_Worksheet' failed". The code that I
received was as follows:
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
'
'
'
This code sandwiched in with other code as shown below:
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 in advance.



--
Kent Lysell
IBM Consultant
Ottawa, Ontario

Coded to delete extra rows after pivot table
 

Thanks Jim.

It didn't seem to work.... the reason I want to delete all rows after
macro-generated pivot tables is because I have formulas running down past the
pivot tables to accommodate variance columns. I wasn't able to add variance
columns as a pivot table calculated item since I have too much data and it
would subsequently crash. Therefore, I have a formula-driven variance columns
immediately to the right of my macro-generated pivot table.

The problem I am faced with is that these formulas (which will indicate ""
as a value in rows below my pivot table) mess up my print macro (activated
through a macro button on this pivot table worksheet). Rather than printing -
say 2 pages containing the pivot table, 53 pages are printed. These 53 pages
capture the actual pivot table (copied over from the master pivot table) plus
these unwanted blank rows containing the variance formulas.

Is there a way to alter the code that you gave me to achieve this desired
result? i.e. of enabling my print macros to print up only the macro-generated
pivot tables and not all these blank rows?

Thanks very much!
Kent.
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Jim Thomlinson" wrote:

Here is some code that I use... Just call CompactSheet. If you do not supply
a worksheet then it works on the active sheet...

Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


"klysell" wrote:

I tried to use some code given to me on this site to delete rows after
macro-generated pivot tables, but this did not work. I received an error
message, "Method 'Range' of Object '_Worksheet' failed". The code that I
received was as follows:
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
'
'
'
This code sandwiched in with other code as shown below:
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 in advance.



--
Kent Lysell
IBM Consultant
Ottawa, Ontario