(Solved) – Row Insert is slow inside a loop

(solved)-–-row-insert-is-slow-inside-a-loop

I need to insert rows in a worksheet.

I have to insert around 350 rows based on some condition and it takes around 30-40 minutes.

Below is my VBA code:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

For index = CardetailInfoStartRow To (CardetailInfoStartRow   CardetailRecordCount - 1)
    If IsError(CardetailDistance) = False Then
        If Len(Trim(CardetailDistance)) > 0 Then                    
            Sheets("Cars").Rows(rowIndexToInsert).Insert Shift:=xlDown
            Sheets("Cars").Range("B" & rowIndexToInsert & ":EA" & _
              rowIndexToInsert).Value = "Cardetail " & _
              Sheets("Cars").Range("I" & index).Value & ", " & CardetailDistance
            Sheets("Cars").Range("B" & rowIndexToInsert & ":EA" & rowIndexToInsert).Select
            With Selection
                .VerticalAlignment = xlTop
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = True
                .RowHeight = 23                                
            End With
            rowIndexToInsert = rowIndexToInsert   1
        End If
    End If
Next index

The sheet contains some conditionally formatted cells.

Going through some of the solutions, there was this solution to disable the conditional formatting. I tried using VBA and still the performance has not improved. Below code was inserted before the method execution.

Range("F1:EA" & Range("car_count").Value - 1).Select
Selection.Interior.ColorIndex = xlNone
Selection.Cells.FormatConditions.Delete

Would there be any alternatives to improve the performance?

Leave a Reply

Your email address will not be published. Required fields are marked *