如何根据单元格值在Excel中删除一行? [英] How to remove a row in Excel based on cell value?

问题描述

我有一个 Excel，我想根据特定的单元格值删除一些行，但 Excel 非常非常大，大约有 75000 行.我尝试编写一个普通函数来循环遍历工作表，检查单元格值并删除匹配的行，但这需要很长时间.我等了 30 分钟，程序仍在运行，有人可以建议我如何实现这一点.

I have an Excel and I want delete some rows based on the particular cell value, but the Excel is very very huge it has got around 75000 rows. I tried writing a normal function to loop through the sheet, check for the cell value and delete the rows if matched, but it is taking very long time. I waited for 30 min and program was still running can some one suggest me how to achieve this.

以下是代码.

Private Sub CommandButton1_Click()
    Dim i As Integer

        For i = Range("D1").End(xlDown).Row To 1 Step -1
                If Cells(i, 4) = 7 Then
                    Rows(i).Delete Shift:=xlUp
                End If
        Next i
End Sub

我针对 50 行的小型 Excel 文件测试了此代码，并且运行良好.但是当我尝试使用 excel 时，我想尝试它让我等待 30 分钟并且无法完成.

I tested this code for small Excel file with 50 rows and it is working fine. But when I try on the excel I wanted to try it kept me waiting for 30 min and could not complete.

推荐答案

如果要删除 D 列值为 7 且 D 列中没有空格的行，请运行:

If you want to delete rows in which column D has the value 7 and there are no blanks in column D, then run:

Sub Tachyon()
    Dim rD As Range
    Set rD = Intersect(Range("D:D"), ActiveSheet.UsedRange)
    rD.Replace "7", ""
    rD.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

EDIT1:

不要使用这个宏

测试表明，它不仅会删除 D 列中包含 7 的行，还会删除其单元格包含 77 或 777 的行.它还破坏了 17 或 71 等值.我们需要更好的东西.

Testing indicates that not only does it remove rows containing 7 in column D, it also removes rows whose cells containing 77 or 777. It also corrupts values like 17 or 71. We need something better.

EDIT2

此版本已修复问题:

Sub Tachyon2()
    Dim rD As Range
    Set rD = Intersect(Range("D:D"), ActiveSheet.UsedRange)
    rD.Replace What:="7", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    rD.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

这篇关于如何根据单元格值在Excel中删除一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！