Excel VBA插入空白行花费的时间太长 [英] Excel vba to Insert blank rows is taking too long

问题描述

我有一个宏，用于比较2个单元格，如果两个单元格不同，则在它们之间插入空白行.使用以下代码完成此过程大约需要12分钟:

I have a macro that is comparing 2 cells and inserting a blank row between them if they are different. It was taking about 12 minutes to complete this process with this code:

    Worksheets("Dollars").Activate
    Range("B10").Select

'    Do Until ActiveCell.Formula = ""
'        DoEvents
'        If ActiveCell <> ActiveCell.Offset(1, 0) Then
'            ActiveCell.Offset(1, 0).Activate
'            Selection.EntireRow.Insert
'        End If
'        ActiveCell.Offset(1, 0).Activate
'    Loop

我将代码重写为这种方式，以查看它是否更好，并且仍然需要12分钟才能运行.

I rewrote the code to this way to see if it was any better and it still took over 12 minutes to run.

    Dim r As Long
    Dim vStr1 As String
    Dim vStr2 As String
    r = 10
    vStr1 = ""
    vStr2 = ""

    Do Until Len(Trim(Cells(r, 2))) = 0
        DoEvents
        vStr1 = ""
        vStr2 = ""
        vStr1 = Trim(Cells(r, 2))
        vStr2 = Trim(Cells((r + 1), 2))

        If vStr1 = vStr2 Then
'           do nothing
        Else
            Cells((r + 1), 1).EntireRow.Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            r = r + 1
        End If
        r = r + 1
    Loop

有没有更好的方法可以做到这一点，所以用不了那么长时间?我们正在使用Windows 10和Office2016.感谢您的帮助.我很感激....

is there a better way to do this so it doesn't take so long? We are using Windows 10 and Office 2016. Thanks for the help. I appreciate it....

推荐答案

通常来说，在Excel中插入很多行是PITA性能的明智之举.
您应该考虑在列表末尾添加行，并在流程结束时对整个列表进行排序.
我知道这是一个简短的答案，但这就是我现在可以从Chromebook所能完成的全部工作...

Generally speaking inserting a lot of rows in Excel is a PITA performance wise.
You should consider adding rows at the end of your list and sorting the whole list at the end of the process.
I know it's a bit short answer but it's all I can do from my Chromebook now...

这篇关于Excel VBA插入空白行花费的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！