快速删除行的VBA宏 [英] VBA macro to delete rows quickly

问题描述

我有几个非常大的 excel 数据文件，我需要遍历它们并删除 T 列中单元格值为 1 的所有行.现在我的代码如下所示:

I have several really large excel data files and I need to go through them all and delete all rows where the value of the cell in column T is 1. Right now my code looks like:

Sub test()
    Dim cell As Range

    For Each cell In Worksheets("Sheet1").Range("T5", "T900000")
        If cell.Value = 1 Then
            cell.EntireRow.Delete
        End If
    Next cell
End Sub

它似乎有效，但需要很长时间才能运行，而且我将不得不多次执行此操作.有没有更好的方法来做到这一点，或者有什么方法可以优化我已经拥有的东西以使其运行得更快?

It seems to be working, but takes forever to run and I'm going to have to do this a bunch of times. Is there a better way of doing this, or some way to optimize what I already have to make it run faster?

推荐答案

这并不像您想的那样工作...当您在遍历行时删除行，最终会跳过行.示例:假设您的行在 A 列中包含数字 1...10.您查看第一行并决定将其删除.现在你看第二行.它有数字 3！你从没看过第 2 行！！

This doesn't work as you think... When you delete rows as you iterate through them, you end up skipping rows. Example: imagine your rows have the numbers 1...10 in column A. You look at the first row and decide to delete it. Now you look at the second row. It has the number 3! You never looked at row 2!!

更好的方法是根据 T 列的条件过滤电子表格，复制它，将其粘贴到新的工作表(带有格式等).

Better method would be to filter the spreadsheet on your criteria for column T, copy it, paste it I to a new worksheet (with formatting etc).

您可以打开宏录制并手动执行此操作；那么您将拥有确切的 VBA 代码.我相信那会快得多.

You can turn on macro recording and do this manually; then you will have the exact VBA code. I am sure that will be much faster.

即使你不这样做，如果你想为每个删除东西的地方做一个，倒序(从最后开始，向后工作)

Even if you don't do that, if you want to do a for each where you delete things, reverse the order (start at the end and work backwards)

这篇关于快速删除行的VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！