Formatting Cells after Insert very slow

maecky

New Member
Joined
May 5, 2014
Messages
11
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...

Code:
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:
Code:
Call FormatCells(r_numeration,  1)

But if I call it with an recently inserted row, it takes way to long:
Code:
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:
Code:
Application.ScreenUpdating = false
Application.EnableEvents = false
Application.DisplayStatusBar = false

BTW. r_numeration is a Type i defined:
Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

maecky

New Member
Joined
May 5, 2014
Messages
11
Forgot to mention that I already did this, didn't increase the speed..

Code:
Application.Calculation = xlCalculationManual
 

Watch MrExcel Video

Forum statistics

Threads
1,109,366
Messages
5,528,269
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top