Can formatting be done faster (vba)?

[Kappy]

I have the following code setup to easily print out a report that a system at my work generates..

It takes about 5 seconds to run, which I know is not soooo long.. but it seems like it should be nearly instaneous given the simplicity of the task.

Any suggestions on why its taking so long would be appreciated. Thanks.

Sub srPrintFormat()
Dim LastRow As Integer, LastColumn As Integer
Application.ScreenUpdating = False
LastRow = Range("C" & Rows.Count).End(xlUp).row
LastColumn = Cells(11, Columns.Count).End(xlToLeft).Column - 1
With ActiveSheet.PageSetup
.PrintArea = Range("C4", Cells(LastRow, LastColumn)).Address
.PrintTitleRows = "$4:$7"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.Orientation = xlPortrait
End With
Application.ScreenUpdating = True
End Sub

 

[mikejvir]

Did you try ISY code? It does what I do for woksheets what I do for charts by turning off various controls then turning them back on afterwards. If that is taking 8 to 10 seconds, download and install CutePDF. Then before running the macro print a page to the CutePDF printer (this sees the printer for a local machine printer). Now run the macro. It should be less than 2 seconds.

Mike Virostko

 

[zookeepertx]

No, never even HEARD of ISY code! I'm somewhat "technology illiterate", so a lot of things sound greek to me.

I'm not allowed to download &/or install anything on my work computer, unfortunately. (actually I can't download on my home computer either, but that's because I'm on dialup there; cave painting, I know! )

Jenny

 

[mikejvir]

ISY is the user who posted code before for the Sub srPrintFormat(). You may not be able to download, but you can copy and paste.

Mike Virostko

 

[zookeepertx]

ISY is the user who posted code before for the Sub srPrintFormat().

Oh, duhhhh! I'm so slow sometimes...

I'll give that a try later. Thanks!

Jenny

 

[zookeepertx]

Hmmm, I tried pasting the part of that code that seemed to refer to the page setup into my macro & it didn't change the speed any. Of course, if waiting 8 secs or so is the WORST thing that happens to me, I'm having a good week, LOL! But if it can be speeded up, then why not, right?

Here's the code as it stands now. The part that slows it down is (hopefully) bolded; at least that's what I tried to do.

Thanks!

Jenny

Sub ClearingTB()
' ClearingTB Macro
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
 
    Cells.Select
    With Selection
        .WrapText = False
        .MergeCells = False
        .Font.Name = "Tahoma"
        .Font.Size = 7
        .RowHeight = 10.5
    End With
    With Columns("E:R")
        .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        .HorizontalAlignment = xlRight
        .EntireColumn.AutoFit
    End With
    With Columns("B:B")
        .NumberFormat = "0"
        .HorizontalAlignment = xlLeft
    End With
    Columns("A:A").ColumnWidth = 2.14
    Columns("S:T").Delete Shift:=xlToLeft
    Rows("9:11").Cut
    Rows("6:6").Select
    Selection.Insert Shift:=xlDown
    Rows("10:12").Delete Shift:=xlUp
 
    LastRow = Range("E" & Rows.count).End(xlUp).Row
    LastColumn = Cells(6, Columns.count).End(xlToLeft).Column
[B]   With ActiveSheet.PageSetup[/B]
[B]       .PrintTitleRows = "$1:$7"[/B]
[B]       .PrintArea = Range("A1", Cells(LastRow, LastColumn))[/B]
[B]       .LeftMargin = Application.InchesToPoints(0)[/B]
[B]       .RightMargin = Application.InchesToPoints(0)[/B]
[B]       .TopMargin = Application.InchesToPoints(0)[/B]
[B]       .BottomMargin = Application.InchesToPoints(0)[/B]
[B]       .HeaderMargin = Application.InchesToPoints(0)[/B]
[B]       .FooterMargin = Application.InchesToPoints(0)[/B]
[B]   End With[/B]
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
 
End Sub

 

[mikejvir]

Hi Jenny,

I am running Windows XP and Excel 2007 SP2 on a Dell laptop with 4 GB of RAM and a 2.53 GHz Duo CPU.

Excel would not recongize the following line

.PrintArea = Range("A1", Cells(LastRow, LastColumn))

I comment this out and it rang in 1.5 seconds.

Mike Virostko

 

[zookeepertx]

Hi Mike,

I tried removing that row & it didn't help, so I tried removing the PrintTitleRows = "$1:$7", which didn't help, either.
So, I put them both back & ran the whole code, one step at a time & it really goes fine right up until the first marging line - .LeftMargin = Application.InchesToPoints(0). That's where it slows down every time. It seems like such a simple command, too, compared to some of the stuff that other macros do in just a flash!

Oh, almost forgot to say that I'm stuck with Excel 2003.

Jenny