• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Formating code consuming huge amount of time

J

Jagdev Singh

Active Member
Hi Experts

The below code is macro recorded and working fine, but consuming huge time to excute. Could you please help me to shrink the below code.

Code: 
Sub PivotFormating()
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
With ActiveSheet.PivotTables("PivotTable1")
  With .PivotSelect("'Notes'[All]")
      With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
  End With
  With .PivotSelect("'Ins'")
      With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
  End With
  With .PivotSelect("'Date'")
      With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
  End With
  With .PivotSelect("'DueDate'")
        With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
    End With
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End With
    ActiveWindow.Zoom = 90
    Columns("A:A").ColumnWidth = 30
    Columns("B:B").ColumnWidth = 14.43
    Columns("C:C").ColumnWidth = 16.43
    Columns("D:D").ColumnWidth = 23.29
    Columns("E:E").ColumnWidth = 20.86
    Columns("F:F").ColumnWidth = 19.43
    Columns("G:G").ColumnWidth = 16.43
    Columns("H:H").ColumnWidth = 30
    Columns("H:H").Select
    Selection.Style = "Comma"
    Columns("I:I").Select
    Selection.Style = "Comma"
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
Application.ScreenUpdating = True
End Sub
 
What happens if you don't include these two lines at the top?

Code: 
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
 
If you step through the code using F8
which is the section or line/s that are slow?

can you post the file ?
 
Hi Hui

It gets slow down in the code .WrapText and .VerticalAlignment wherever it is appearing in the code
Code: 
  With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
Regards,
JD
 
Hi Jaggi ,

As far as I can see , your code can be simplified :

1. First set the column widths

2. Use the PivotSelect method to select the data and labels and format them with all of the common properties :

Code: 
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False

3. Only for the two date columns , set the WrapText property to True.

Narayan
 
Hi Narayan

I amended the code as per your above instruction and it reduce the overall process time of formating from 4 mins to 2 mins.

I will see if I can amened the code further to reduce the time further.

Thanks for the instruction.

Regards,
JD
 
You must log in or register to reply here.
Back
Top