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?