删除具有超过60000行的工作表中的具体行 [英] Delete specifics rows in a sheet with more than 60000 rows

问题描述

我需要删除行：
- 没有Word获取到列A，例如：如果A1是配置获取，我不应该删除;但如果是nFormat或其他任何东西，我应该删除。
- 对于具有单词get的行，我还需要检查列C中的值是否为0，如果不是0，我也应该删除。

I need to delete the rows that: - Does not have the Word "Get" into Column A, example: if A1 is Configuration Get, I should not delete; but if it is nFormat or anything else, I should delete. - And for the rows which has the word "get" I also need to check if in Column C the value is 0, if it is not 0 I also should delete.

我的功能正在使用少量行的工作表，但问题是，我真的需要运行它大量，假设为60000行。有人可以帮助我吗？

My function is working for sheet with a small number of rows, but the problem is, I really need to run it for a large number, let's say for 60000 rows. Could someone help me?

我的功能是：

Sub DeleteRows()

   Dim c As Range
   Dim ColumnA
   Dim Lrow As Long
   Dim Lastrow As Long

With Sheets("Sheet1") 'I'm using the Sheet1
.Select

   Set ColumnA = ActiveSheet.UsedRange
   Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = Lastrow To 1 Step -1

Set ColumnA = Cells(Lrow, "A") 'I'm looking just in Column "A" for a Get

       Set c = ColumnA.Find("Get", LookIn:=xlValues)
       If Not c Is Nothing Then
            ' If the cell has a Get, it will look for a 0 in Column "C"
            With .Cells(Lrow, "C")
                If Not IsError(.Value) Then
                    ' If the Value is not 0 the row will be delete.
                    If Not (.Value = 0) Then .EntireRow.Delete
                End If
            End With

        Else
        'If didn't find a "Get", it will delete the row
        ColumnA.EntireRow.Delete

        End If      
Next Lrow

End With

End Sub

推荐答案

尝试使用 AutoFilter 代替

这是VBA相当于：

1. 找到第一个空白栏


2. 输入 = OR（ISERROR（FIND（Get，$ A1）），AND（NOT（第1行中的ISERROR（FIND（Get，$ A1））），$ C1<> 0））删除<$ / $> $ b code> TRUE 结果


3. 清理工作栏

1. finding the first blank column
2. entering =OR(ISERROR(FIND("Get",$A1)),AND(NOT(ISERROR(FIND("Get",$A1))),$C1<>0)) in row 1 and copying down
3. deleting and TRUE results
4. cleaning up the working column

Sub KillEm()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
    Application.ScreenUpdating = False
    With rng3.Offset(0, 1)
        .FormulaR1C1 = "=OR(ISERROR(FIND(""Get"",RC1)),AND(NOT(ISERROR(FIND(""Get"",RC1))),RC3<>0))"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .Offset(1, 0).Resize(rng3.Rows.Count - 1, 1).EntireRow.Delete
        .EntireColumn.Delete
    End With
    Application.ScreenUpdating = True
End Sub

这篇关于删除具有超过60000行的工作表中的具体行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！