需要关于如何加快这一点代码的建议

问题描述

我有一个电子表格，可以从3种不同类型的文件导入一堆数据。它将格式化，以使所有三者的输出相同，在一种情况下，需要根据该行的单元格中的内容删除行。问题是，与其他文件（没有梯级删除的文件）的格式化所需的时间相比，这需要大约1.5到2分钟。工作表开始大约4000行，删除约1600行。我目前的做法很好，一切都有效，但是有一种方法可以运行得更快吗？

  rw = 1 
 Lastrow = 2600 
 NewRow = 0 
 
 NxtChk2：
 If（Worksheets（ARCSRT.CND）。Cells（rw，c）> =0）And（Worksheets（ARCSRT.CND）。Cells（rw，d）=）然后
工作表（ARCSRT.CND）。Rows（rw）.EntireRow.Delete 
 rw = rw  -  1 
 End If 
 If（Worksheets（ARCSRT.CND）。Cells（rw，b）=0.0）And（Worksheets（ARCSRT。 CND）。单元格（rw，c）=）然后
工作表（ARCSRT.CND）。Rows（rw）.EntireRow.Delete 
 rw = rw  -  1 
结束如果
如果rw = Lastrow然后GoTo LATER 
 rw = rw + 1 
 GoTo NxtChk2 
  

解决方案

从我的评论以下，尝试以下

这个循环前：

Dim rng As Range

设置rng =没有

在循环中而不是行删除，请执行以下操作：

如果rng = Nothing Then

rng =细胞（rw，A）

其他

rng = Union（rng，Cells（rw，A））

End If

然后循环结束后：

rng.EntireRow.Delete

请记住，我不知道如何在1600个不连续的行上运行，在备份副本上运行

I have a spreadsheet that can import a bunch of data from 3 different types of files. It formats it so that the output will be the same for all three, in one case it needs to delete rows based on what is in the cells of that row. The problem is that this takes about 1.5 to 2 minutes to do compared to seconds it takes for the formatting of the other files (the ones without rung deletes). The worksheet starts out around 4000 rows and deletes about 1600 of them. My current way of doing it works fine and everything works but is there a way of doing this that would run faster?

rw = 1
Lastrow = 2600
NewRow = 0

NxtChk2:
    If (Worksheets("ARCSRT.CND").Cells(rw, "c") >= "0") And (Worksheets("ARCSRT.CND").Cells(rw, "d") = "") Then
        Worksheets("ARCSRT.CND").Rows(rw).EntireRow.Delete
        rw = rw - 1
    End If
    If (Worksheets("ARCSRT.CND").Cells(rw, "b") = "0.0") And (Worksheets("ARCSRT.CND").Cells(rw, "c") = "") Then
        Worksheets("ARCSRT.CND").Rows(rw).EntireRow.Delete
        rw = rw - 1
    End If
    If rw = Lastrow Then GoTo LATER
    rw = rw + 1
GoTo NxtChk2

解决方案

following from my comment, try the following
this to go before the loop:
Dim rng As Range

Set rng = Nothing

in the loop instead of row delete do this:

If rng = Nothing Then
rng = Cells(rw, "A")
Else
rng = Union(rng, Cells(rw, "A"))
End If

and then after the loop has finished:

rng.EntireRow.Delete

bear in mind i am not sure how will it run on 1600 non-consecutive rows, run on a backup copy

这篇关于需要关于如何加快这一点代码的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！