Need suggestions on how to speed up this bit of co

2019-09-19 12:30发布

This question already has an answer here:

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

标签: excel vba
2条回答
Lonely孤独者°
2楼-- · 2019-09-19 12:44

If sorting of the list is an option, instead of deleting row one by one just mark them with a "flag", i.e. letter "X" in a separate column, once the cycle finished, get your code to sort data on that column and just delete the whole batch of flagged rows in one go, it will do the deletion operation once and not 1600 times.

查看更多
迷人小祖宗
3楼-- · 2019-09-19 12:53

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

查看更多
登录 后发表回答