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

问题描述

我有几个非常大的ex​​cel数据文件，我需要遍历它们，并删除列T中单元格的值为1的所有行。现在我的代码看起来像：

  Sub test（）
 Dim cell As Range 
 
对于Worksheets（Sheet1）中的每个单元格Range（ T5，T900000）
如果cell.Value = 1然后
 cell.EntireRow.Delete 
 End If 
下一个单元格
 End Sub 
  

它似乎正在工作，但永远需要运行，我将不得不这样做一次。有没有更好的方法来做这个，或者某种方式来优化我已经使它运行得更快的方法？

解决方案

不按照你的想法工作...当你遍历它们时删除行，你最终会跳过行。示例：假设您的行在列A中的数字为1 ... 10，您将看到第一行并决定删除它。现在你看第二行。它有3号！你从来没有看过第2行!!

更好的方法是过滤电子表格的标准，列T，复制它，将其粘贴到一个新的工作表（带格式化等）。

您可以打开宏录制并手动执行;那么你将具有确切的VBA代码。我相信这会更快。

即使你不这样做，如果你想为每个你删除的东西，颠倒的顺序（从最后开始和后退）

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?

解决方案

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!!

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).

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屋！