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.