时间戳

首页 > 解决方案 > 行插入在循环内很慢

excel - 行插入在循环内很慢

问题描述

我需要在工作表中插入行。

我必须根据某些条件插入大约 350 行，大约需要 30-40 分钟。

下面是我的 VBA 代码：

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

该工作表包含一些条件格式的单元格。

通过一些解决方案，有这个解决方案可以禁用条件格式。我尝试使用 VBA，但性能仍然没有提高。下面的代码是在方法执行之前插入的。

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

是否有任何替代方法可以提高性能？

标签： excelvbaconditional-formatting

解决方案

根据评论，我能够为此实施解决方案。不是逐个插入行，而是通过计算需要插入的行数来完成单个插入。现在执行不到一分钟

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

If CarDetailInfoStartRow > 0 And CarDetailRecordCount > 0 Then
            Dim recCount As Integer
            recCount = 0

            For index = CarDetailInfoStartRow To (CarDetailInfoStartRow + CarDetailRecordCount - 1)
                CarDetailSplitLimit = Sheets("Cars").Range("BF" & index).Value
                If IsError(CarDetailSplitLimit) = False And Len(Trim(CarDetailSplitLimit)) > 0 Then
                    recCount = recCount + 1
                End If
            Next index
            If recCount > 0 Then
                Sheets("Cars").Rows(rowIndexToInsert).EntireRow.Offset(1).Resize(recCount).Insert Shift:=xlDown
            End If


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

推荐阅读