1

I have what I think is a pretty short VBA excel script that basically copies data to another sheet if there is data there and then displays it how I need it displayed to print.

It runs really slow

As you can see I have tried to turn off auto calculation and screen updating. This I think speeds it up a little. But it still take several minutes on what I think should take a second.

    Sub Button2_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Worksheets("sheet2").PageSetup
        .PaperSize = xlPaperStatement
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(1.5)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(1.25)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
End With



Dim rows, colum, length, i, a, b, c As Integer
length = Worksheets("Sheet1").Cells(Worksheets("Sheet1").rows.Count, "A").End(xlUp).Row
i = 1
    For rows = 3 To length
        For colum = 4 To 6
            If colum = 5 Then
        GoTo NextIteration
            End If
            If IsEmpty(Worksheets("Sheet1").Cells(rows, colum)) Then
            GoTo NextIteration
            Else
            Worksheets("Sheet2").rows(i).RowHeight = 90
            Worksheets("Sheet2").rows(i + 1).RowHeight = 3.6
            Worksheets("Sheet2").rows(i + 2).RowHeight = 79.6
            Worksheets("Sheet2").rows(i + 3).RowHeight = 93.2
            a = Len(Worksheets("Sheet1").Cells(rows, colum))
            b = InStr(1, Worksheets("Sheet1").Cells(rows, colum), " ")
            c = a - b + 1
            Worksheets("Sheet2").Cells(i, 2).Value = Mid(Worksheets("Sheet1").Cells(rows, colum), InStr(1, Worksheets("Sheet1").Cells(rows, colum), " "), c)
            Worksheets("Sheet2").Cells(i + 2, 2).Value = Format(Worksheets("Sheet1").Cells(rows, 1), "Medium Time")
            i = i + 4
            End If
NextIteration:
        Next colum
    Next rows

Worksheets("Sheet2").Columns("A:A").ColumnWidth = 13
Worksheets("Sheet2").Columns("B:B").ColumnWidth = 77
Worksheets("Sheet2").Columns("B:B").Font.Name = "David"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Is it possible that having the view mode set to page layout would make it slow down?

I have switched it back to normal view mode and it works almost instantly.

Improve this question
8
  • 3
    Printer communication can slow things down; particularly so if it a wireless printer, a network printer or one that goes into 'standby' mode. Set your printer first (preferably to a write-to-pdf or write-to-file) and optimize your code. Everything after that is simply printer communication lag. Commented Apr 24, 2016 at 1:33
  • What's the typical value of length (number of rows in Sheet1)? Commented Apr 24, 2016 at 1:33
  • So I did try to change the printer to a write-to-pdf and it helped some more so yea! for continuous improvements. Typical length is 38 rows to maybe 60 rows at the most. Commented Apr 24, 2016 at 1:37
  • btw, while the deliberate mispelling of colum removes it from confusion with the Range.Column property, there is both a Worksheet.Rows and a Range.Rows property that could generate ambiguity. Commented Apr 24, 2016 at 1:39
  • 1
    Fully agree with @Jeeped. As a note, some of the .PageSetup properties can be extremely slow. If you can just run that code block once rather than on each click, then your routine will probably speed up considerably. Commented Apr 24, 2016 at 3:06

3 Answers 3

Reset to default
2

the issue is rowheight setting.

it's best done in one shot instead of row by row

consider the following code

Option Explicit

Sub Button2_Click()

' here goes your code for page settings
' ...


Dim iRow As Long, j As Long, a As Long, b As Long
Dim cell As Range
Dim sht2Rows As String, sht2RowsHeight As Variant
Dim myVal As Variant
Dim sht1 As Worksheet, sht2 As Worksheet

'set a reference to your sheets once and for all!
Set sht1 = Worksheets("Sheet1")
Set sht2 = Worksheets("Sheet2")

sht2RowsHeight = Array(90, 3.6, 79.6, 93.2) ' set needed rows height

iRow = 1
For Each cell In sht1.Range("A3", sht1.Cells(sht1.rows.Count, "A").End(xlUp)) 'loop through "Sheet1" column "A" from row 3 to the last non blank row
    For j = 3 To 5 Step 2 'consider corresponding cells in columns "D" and "F", obtained as offsetted from "A"
        If Not IsEmpty(cell.Offset(, j)) Then
            sht2Rows = sht2Rows & "A" & iRow & "," 'update cells references whose row height is to be set
            myVal = cell.Offset(, j).Value 'store cell value for subsequent operations with it
            a = Len(myVal)
            b = InStr(1, myVal, " ")
            sht2.Cells(iRow, 2).Value = Mid(myVal, b, a - b + 1)
            sht2.Cells(iRow + 2, 2).Value = Format(cell, "Medium Time")
            iRow = iRow + 4
        End If
    Next j
Next cell

' format Sht2 rows and columns
With sht2
    'format rows height
    For j = 0 To 3
        .Range(Left(sht2Rows, Len(sht2Rows) - 1)).Offset(j).RowHeight = sht2RowsHeight(j)
    Next j

    'format Columns width
    .Columns("A:A").ColumnWidth = 13
    With .Columns("B:B")
        .ColumnWidth = 77
        .Font.name = "David"
    End With
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

it stores in sht2Rows all references of the "first" rows to be formatted and then format all "four" rows in 4 shots, each conveniently offsetting from the "first" one

it also does some code cleaning and variables usage optimization

also consider always using Option Explicit at the very topo of any module: at the expense of some extra work to dim all variables you'll gain much more control over your code and debugging time shortening down

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

ActiveSheet.DisplayPageBreaks = False (in normal view) makes any size changes lightning fast

Improve this answer

1 Comment

Unbelievable speed difference! From 30 sec down to 2 sec. Thank you so much!!!
0

What really worked the best for me was to switch the view mode back to normal from page layout view. I don't know why but it now takes 2 seconds compared to a minute or more.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.