VBA更优化:过滤隐藏的行

Question

>我需要过滤超过 50000 行的 excel 工作表 512 次。我目前正在使用以下代码。

Do While Not IsEmpty(ActiveCell.value)
        Worksheets("Sheet1").Activate
        filtro = ActiveCell.value
        Sheets("Sheet2").Select
        Range("D1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$I$" & lastRow("D")).AutoFilter Field:=4, Criteria1:=filtro
        Range("A1").Select
        Do While Not IsEmpty(ActiveCell.value)
            Do
                ActiveCell.offset(1, 0).Select
                oProgress.Increase 1
            Loop While ActiveCell.EntireRow.Hidden = True
                Call functio ' this function do something with the actual row
        Loop
    Loop

问题是分析 50000 行 X 512 次需要花费很多时间！，我在想也许最好过滤然后将行复制到临时工作表中，并检查那里的值？

活动行由函数 readValues 处理

Answer 1

像这个问题的其他海报一样，我真的对过滤512次持怀疑态度！但是，既然你说你绝对需要它，请参阅下面的代码。我认为仍然可以根据您的需求对其进行清理，但是如果不看到更大的范围，很难说。然而，它应该把你带到一个比现在更有效的地方。

我

试图评论我所有的假设，但如果您需要更多解释，请联系。

Sub FilterAll512()
Dim wks1 As Worksheet
Set wks1 = Sheets(1)
Dim rng512 As Range, cel As Range
Set rng512 = wks1.Range("A1:A512") '-> say your filter values are in this range, adjust if needed
Dim wks2 As Worksheet
Set wks2 = Sheets(2)
For Each cel In rng512
    With wks2
        Dim rngFound As Range
        Set rngFound = .Columns(4).Find(cel.Text, LookIn:=xlWhole) ' -> make sure value is there to be filtered on
        If rngFound Then
                .UsedRange.AutoFilter 4, cel.Text '-> assumes upper left most cell is A1
                Dim rngSearch As Range, rngCel As Range
                '-> assumes upper left most cell is A1 and row 1 has headers
                Set rngSearch = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(4).EntireColumn).SpecialCells(xlCellTypeVisible)
                For Each rngCell In rngSearch
                    oProgress.Increase 1 '-> i don't know what this does, but it doesn't look very efficient, see notes below
                    Call functio
                Next
                '-> perhaps above you are just counting rows?
                    'if so, you can use rngSearch.Rows.Count to get a total count
                    'or you can use rngCell.Row to get the current row

        End If 'iF rngFound Then
    End With ' With wks2
Next 'For Each cel In rng512
Set rngCell = Nothing
Set rngSearch = Nothing
Set rngFound = Nothing
Set cel = Nothing
Set rng512 = Nothing
Set wks2 = Nothing
Set wk1 = Nothing
End Sub

Answer 2

Excel内置了过滤工具（大多数人会将其描述为Excel的一个非常基本的功能）。即使你确实必须循环访问 500,000 行，这很可能是你能做到的最糟糕（最慢）和最奇怪的方法之一：

Do                 
    ActiveCell.offset(1, 0).Select 
Loop While ActiveCell.EntireRow.Hidden = True 

你可以看看 Range（）。排序和范围（）。自动筛选 这是正确的方法。祝你好运。

Answer 3

您可以做的一个想法是计算所有行号或行号范围，然后应用如下代码：

rowNumbers = "1:3,10:10,30:50"
Range(rowNumbers).EntireRow.Hidden = True