Formatting Cells after Insert very slow

[maecky]

Hi,

I have a problem which I was not able to solve with the help of google.

I have a Sub which formats a row, which runs fast (2 ms) as long as I use it on normal rows but if I insert a new row and then call this Sub it takes 600ms. I cannot figure out, why this is so slow...

Public Sub FormatCells(r_numeration As RNumeration, i_row_nr As Integer)'Format Cells, i.e format the date and string cells...
'Make the borders visible, apply correct fonts and color to the background
'yellow, if it's a chapter row...


    Dim ws As Worksheet
    Set ws = Sheets(str_Protocol)




    ws.Cells(i_row_nr, 1).NumberFormat = "@"
    ws.Cells(i_row_nr, 2).NumberFormat = "@"
    ws.Cells(i_row_nr, 3).NumberFormat = "dd.mm.yyyy"
    ws.Cells(i_row_nr, 4).NumberFormat = "@"
    ws.Cells(i_row_nr, 5).NumberFormat = "@"
    ws.Cells(i_row_nr, 6).NumberFormat = "@"
    ws.Cells(i_row_nr, 7).NumberFormat = "dd.mm.yyyy"
    ws.Cells(i_row_nr, 8).NumberFormat = "dd.mm.yyyy"
    ws.Cells(i_row_nr, 9).NumberFormat = "@"
    ws.Cells(i_row_nr, 9).Value = "o"
    
    'if is chapter -> make background yellow:
    If 1 = r_numeration.i_identifier Then
    'make cells yellow and apply all formatting
        With ws.Range(Cells(i_row_nr, 1), Cells(i_row_nr, 9)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
        End With
        
        'change font
        With ws.Range(Cells(i_row_nr, 1), Cells(i_row_nr, 9)).Font
            .Name = "Arial"
            .Size = 10
            '.ThemeColor = xlThemeColorLight1
        End With
        
        'Center last column
        With ws.Cells(i_row_nr, 9)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
        
        With ws.Range(Cells(i_row_nr, 1), Cells(i_row_nr, 9))
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
            .WrapText = False
            .ShrinkToFit = False
            .AddIndent = False
        End With
        
        'set wrap text true for descritpion and responsibility
        With ws.Range(Cells(i_row_nr, 4), Cells(i_row_nr, 6))
            .WrapText = True
        End With
    Else
    'formatting is used from chapter, make background white and apply borders
        With ws.Range(Cells(i_row_nr, 1), Cells(i_row_nr, 9)).Interior
            '.Pattern = xlSolid
            '.PatternColorIndex = xlAutomatic
            .Color = vbWhite
        End With
    End If




    With ws.Range(Cells(i_row_nr, 1), Cells(i_row_nr, 9)).Borders
        .LineStyle = xlContinuous
    End With


    'select introduction date cell, to proceed
    ws.Cells(i_row_nr, 3).Select
    
End Sub

If I call this Sub on any row in the sheet it works pretty fast:

Call FormatCells(r_numeration,  1)

But if I call it with an recently inserted row, it takes way to long:

Sheets(str_Protocol).Rows(rng_result.Row + 1).Insert Shift:=xlDown
Call FormatCells(r_numeration, rng_result.Row + 1)

I already disabled everything I could think of slowing down the code:

Application.ScreenUpdating = false
Application.EnableEvents = false
Application.DisplayStatusBar = false

BTW. r_numeration is a Type i defined:

Public Type RNumeration
    i_chapter As Integer
    i_section As Integer
    i_sub_section As Integer
    i_identifier As Integer
End Type

I hope someone can help me, I simply don't understand why the same Sub does take so much longer, if I insert a cell and furthermore, after inserting the cell, the else branch of the format function gets executed, therefore far less code is executed...

Thanks for reading so far!

Kind Regards,
m

 

[Andrew Poulsom]

What about setting Calculation to manual?

 

[maecky]

Forgot to mention that I already did this, didn't increase the speed..

Application.Calculation = xlCalculationManual