The Most Effective Way to Add Excel Formatting - VBA

Question

I have a macro that adds hundreds of rows of data to an Excel spreadsheet. I call a procedure from a loop that inserts each row of data. I apply formatting to a string every time I insert this data. However, during my testing, I found that I can insert all the data about 3/4 second faster (3.3 s versus 4.11 seconds) when I do not apply line-by-line formatting, but all at once. The problem I'm trying to overcome is that not every line has the same formatting; however, there is a predictable picture. Two lines of one formatting and one line of different formatting. Is there a way without a loop to apply these two different formats on one that would allow me to maintain the performance gain that I get (users would like to get a second second answer,so this can be a big win).

I am currently using the following code (application settings such as pop-ups, calculations, and events are disabled 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 lines in one template and 1 line in another template. I call it forward 2row and 1row.

You can apply 2cell formatting to the entire column / whole data area, and then cycle only with 1cell formatting.

Answer 2

If you want to avoid using copy / paste, you can use AutoFill to apply formatting to the range.

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

Note. The source range ("A1: F3") must be part of the destination range ("A1: F21")

vba ScreenUpdating. , .

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

Application.ScreenUpdating = True
Application.Calculation = calc

Answer 3

AddDetail(), , 50% . . , , .

.Resize(1, 16) .range(cell (rownum, 3), cell (rownum, 19)), .

- , ,

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

Also, I cannot fully follow your code, you call AddDetail () with rownum and rownum + 1, then you call AddDiff ... () with rownum + 2, but finally you only increase rownum by 2.. If you do not have to increase it by 3 ... or you want to "overwrite" one of the lines created with AddDetail ().

Good luck MikeD