(Solved) – Row Insert is slowing up in VBA

(solved)-–-row-insert-is-slowing-up-in-vba

I have a work sheet to which I need to insert some Rows in between. And this sheet contains some conditional formatting cells as well. The issue here the insert row is so slow to execute and 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

Going through some of the solutions, there was this solution to disable the Conditional formating . I tried that 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 of this.

Leave a Reply

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