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
|
Similar Threads
