Excel VBA running really slow

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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