我需要一个更快的excel vba宏，删除列A中的每一行为0 [英] I need a faster excel vba macro that deletes every row with a 0 in Column A

问题描述

现在，我正在使用下面的宏来删​​除列A中的每一行都是0。问题是它太慢了。大约需要三十秒钟才能完成这两千行的工作，但是我需要一个宏就可以工作30万行。当前的宏使我的电脑冻结了很多行。我已经尝试了这个网站上的前五个解决方案，没有运气： http://www.dummies.com/software/microsoft-office/excel/10-ways-to-speed-up-your-macros/

Right now, I'm using the macro below to delete every row with a 0 in column A. The problem is that it is too slow. It took about thirty seconds to do the job for two thousand rows, but I need a macro to work on 300,000 rows. The current macro freezes my computer with that many rows. I've tried the first five solutions on this site with no luck: http://www.dummies.com/software/microsoft-office/excel/10-ways-to-speed-up-your-macros/

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "0" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

推荐答案

我不能评论这是否是最快的方式，但它可能是最短的您将在以下答案中找到实际代码：

I can't comment on whether this is the fastest way but it's probably the shortest in terms of actual code that you'll find on these answers:

'get number of cells in A column
Dim x as long: x = WorksheetFunction.CountA(ActiveSheet.Range("A:A"))
'AutoFilter to pick up only zeroes
ActiveSheet.Range("$A$1:$Z" & x).AutoFilter Field:=1, Criteria1:=0
'delete what is currently filtered
ActiveSheet.Rows("2:" & x).Delete Shift:= xlUp

编辑：

ActiveSheet.Range("$A$1:$Z" & x).AutoFilter

- 结束后，会自动关闭自动过滤器

-adding this on the end turns the autofilter off afterwards

这里的自动过滤器是按列A排序（字段1在A：Z）和寻找零（标准：= 0） - 可能需要稍微适应您的目的，但它很简单

The autofilter here is sorting by column A (Field 1 in A:Z) and looking for zeroes (Criteria:= 0) - might need adapting slightly for your purposes but it's simple enough

注意：这需要一段时间，30万+行 - 我有一个例程，每两周从一个数据集中取出大约20万行。这可能听起来很疯狂，除了我只是使用数据在数据透视表中进行总结 - 一旦刷新，大部分数据可以走。

note: This does take a while with 300,000 + rows - I have a routine which takes out about 200,000 + rows out of a data set like this on a bi-weekly basis. Which probably sounds mad, except I'm only using that data to summarise it in a Pivot Table - once that's been refreshed, most of the data can go.

这篇关于我需要一个更快的excel vba宏，删除列A中的每一行为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！