Most Efficent Way to Add Formatting Excel - VBA

Question

I have a macro that add hundreds of lines of data to an excel spreadsheet. I call a procedure from a loop which inserts each line of data. I have been applying the formatting for the row each time that I insert that data. However, during my testing I have found that I can insert all of the data about 3/4 of second faster (3.3 sec vs. 4.11 sec) when I don’t apply the formatting line by line but all at once. The issue that I am trying to overcome is that not every row has the same formatting; however, there is a predictable pattern. Two rows of one formatting and one row of different formatting. Is there a way without looping to apply these two different formats all at one that would allow me to keep the performance gains that I am getting (users would like to see a sub 2 second response so this could be a big gain).

I am currently using the following code (application settings such as screenupdating, calculations, and events are all turned off during this)

Private Sub BuildTerminalSummary(ByRef terminals, ByVal timeFrame)
    Dim terminal As clsTerminal
    Dim curSheet As Worksheet
    Dim breakLoop As Boolean
    Dim terminalCode As String
    Dim rowNumber As Long

    Set terminal = New clsTerminal
    Set curSheet = Sheets("Terminal Summary")

    rowNumber = 7

    'Remove all content, borders, and tint
    ClearPage curSheet, rowNumber

    For Each terminal In terminals         
        AddDetailData curSheet, terminal.InfoArray, rowNumber
        AddDetailData curSheet, terminal.PriorInfoArray, rowNumber + 1
        AddDiffPercentFormulas curSheet, terminal.DiffPercentInfoArray, rowNumber + 2

        rowNumber = rowNumber + 2

    Next terminal

    'Make sure the columns are wide enough to display the numbers
    curSheet.Cells.EntireColumn.AutoFit

End Sub

Private Sub AddDetailData(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet
        With .Cells(rowNumber, 3).Resize(1, 16)
            .value = data
            .Style = "Comma"
            .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        End With
        'This overides the formatting in the revenue columns with currency instead of comma style
        With .Cells(rowNumber, 5).Resize(1, 2)
            .Style = "Currency"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
        End With
        With .Cells(rowNumber, 13).Resize(1, 6)
            .Style = "Currency"
        End With
    End With

End Sub

Private Sub AddDiffPercentFormulas(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet.Cells(rowNumber, 3).Resize(1, 16)
        .value = data
        .NumberFormat = "0.00%"
    End With

End Sub

Answer 1

You have two kinds of formatting 2 rows in one pattern and 1 row in another pattern. I'm calling this 2row and 1row formatting.

You could apply 2cell formatting to the entire column / entire data area, and then loop through only 1cell formatting.

Answer 2

you can rewrite AddDetail() to take both arrays in the same call, so you avoid 50% of the calls and are able to format both lines in one shot. That will save lot of time. It's the calls, not the formatting that costs time.

Instead of .Resize(1, 16) try to use .range(cell(rownum, 3), cell(rownum, 19)) which performs faster.

Another fast way is to format first row and copy complete row format to 2nd row, like in

MyRange(row, col).EntireRow.Copy
MyRange(row+1, col).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                                 SkipBlanks:=False, Transpose:=False

Besides that I can't quite follow your code, you are calling AddDetail() with rownum and rownum+1, then you call AddDiff...() with rownum+2, but finally you increment rownum only by2 ... shouldn't you increment it by 3 ... or do you want to "overwrite" one of the lines you created with AddDetail().

Good luck MikeD

Answer 3

If you want to avoid using copy/paste you can use AutoFill to apply formating to a range.

Range("A1:F3").AutoFill Destination:=Range("A1:F21"), Type:=xlFillFormats

Note: The source range ("A1:F3") needs to be a part of the destination range ("A1:F21")

You can also use the usual vba optimization methods of setting calculation to manual and disabling ScreenUpdating . Although I don't think they will make any significant speed-improvements in your case.

Dim calc As XlCalculation
calc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = True
Application.Calculation = calc