在VBA中优化循环逻辑语句 [英] Optimize loop logical statement in VBA

问题描述

我编写了这段代码，从表中删除了特定列中所有不包含单词"ITA"，"GRE"或"CHE"的行.现在，表很大(观察到60k)，循环显然很耗时(5-6分钟).处理任务以优化代码效率(即在10到30秒内执行任务)的另一种方式是什么?

I wrote this code to delete from a table all rows not containing the word "ITA", "GRE" OR "CHE" in a particular column. Now, the table is big (60k observations) and the loop is obviously time consuming (5-6 minutes). What would be another way of tackling the task in order to optimize the efficiency of the code (i.e. performing the task in 10 to 30 seconds)?

Sub test()

 countrycol = UsedRange.Find("Country", lookat:=xlWhole).Column
 For j = 1 To Cells(Rows.Count, countrycol).End(xlUp).Row

 If UsedRange.Cells(j + 1, countrycol).Value <> "ITA" Or UsedRange.Cells(j + 1, countrycol).Value <> "GRE" _
                                         Or UsedRange.Cells(j + 1, countrycol).Value <> "CHE" Then

 UsedRange.Cells(j + 1, countrycol).EntireRow.Delete

 End If

 Next j
End Sub

推荐答案

通过构建不属于country列中值数组的键字典来创建自动筛选器.删除可见行.

Create an AutoFilter by building a dictionary of keys that do not belong from an array of the values in the country column. Delete the visible rows.

sub test2()

    dim i as long, arr as variant, m as variant, dict as object

    set dict = createobject("scripting.dictionary")

    with worksheets("All")

        if .autofiltermode then .autofiltermode = false

        m = application.match("country", .rows(1), 0)
        if iserror(m) then exit sub

        arr = .range(.cells(2, m), .cells(.rows.count, m).end(xlup)).value2

        for i = lbound(arr, 1) to ubound(arr, 1)
            select case ucase(arr(i, 1))
                case  "ITA", "GRE", "CHE"
                    'do nothing
                case else
                    dict.item(arr(i, 1)) = arr(i, 1)
            end select
        next i

        with .cells(1, 1).currentregion
            .autofilter field:=m, criteria1:=dict.keys, operator:=xlfiltervalues
            with .resize(.rows.count-1, .columns.count).offset(1, 0)
                if cbool(application.subtotal(103, .cells)) then
                    .specialcells(xlcelltypevisible).entirerow.delete
                end if
            end with
        end with

        .autofiltermode = false

    end with

end sub

这篇关于在VBA中优化循环逻辑语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！