vba 如何根据条件删除 Excel 工作表中的行

How to Delete Rows in Excel Worksheet based on a Criteria

提问by Maen

I have an excel workbook, in worksheet1 in Column A, IF the value of that column = ERR I want it to be deleted (the entire row), how is that possible?

我有一个 Excel 工作簿，在 A 列的 worksheet1 中，如果该列的值 = ERR 我希望它被删除（整行），这怎么可能？

PS: keep in mind that I have never used VBA or Macros before, so detailed description is much appreciated.

PS：请记住，我以前从未使用过 VBA 或宏，因此非常感谢详细的描述。

回答by niko

  sub delete_err_rows()
      Dim Wbk as Excel.workbook  'create excel workbook object
      Dim Wsh as worksheet       ' create excel worksheet object 
      Dim Last_row as long
      Dim i as long
      Set Wbk = Thisworkbook ' im using thisworkbook, assuming current workbook
                             ' if you want any other workbook just give the name 
                             ' in invited comma as "workbook_name"
      Set Wsh ="sheetname"   ' give the sheet name here 
      Wbk.Wsh.activate
     ' it means Thisworkbook.sheets("sheetname").activate
     ' here the sheetname of thisworkbook is activated
     ' or if you want looping between sheets use thisworkbook.sheets(i).activate
     ' put it in loop , to loop through the worksheets
     ' use thisworkbook.worksheets.count to find number of sheets in workbook
     Last_row = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row 'to find the lastrow of the activated sheet
          For i = lastrow To 1 step -1
             if activesheet.cells(i,"A").value = "yourDesiredvalue"
                  activesheet.cells(i,"A").select  ' select the row
                  selection.entirerow.delete       ' now delete the entire row
             end if
          Next i
   end sub

Note any operations that you do using activesheet , will be affected on the currently activated sheet

请注意，您使用 activesheet 进行的任何操作都将影响当前激活的工作表

As your saying your a begginner, why dont you record a macro and check out, Thats the greatest way to automate your process by seeing the background code

正如您所说的初学者，为什么不录制宏并查看，这是通过查看后台代码来自动化您的过程的最佳方法

Just find the macros tab on the sheet and click record new macro , then select any one of the row and do what you wanted to do , say deleting the entire row, just delete the entire row and now go back to macros tab and click stop recording .

只需在工作表上找到宏选项卡并单击记录新宏，然后选择任何一行并执行您想做的操作，例如删除整行，只需删除整行，然后返回宏选项卡并单击停止记录 。

Now click alt+F11 , this would take you to the VBA editor there you find some worksheets and modules in the vba project explorer field , if you dont find it search it using the view tab of the VBA editor, Now click on module1 and see the recorded macro , you will find something like these

现在单击 alt+F11 ，这将带您到 VBA 编辑器，在那里您会在 vba 项目资源管理器字段中找到一些工作表和模块，如果您没有找到它，请使用 VBA 编辑器的视图选项卡搜索它，现在单击 module1 并查看录制的宏，你会发现这样的东西

        selection.entirerow.delete

I hope i helped you a bit , and if you need any more help please let me know, Thanks

我希望我对你有所帮助，如果你需要更多帮助，请告诉我，谢谢

回答by brettdj

Using an autofilter either manually or with VBA (as below) is a very efficient way to remove rows

手动或使用 VBA（如下）使用自动过滤器是一种非常有效的删除行的方法

The code below

下面的代码

1. Works on the entire usedrange, ie will handle blanks

2. Can be readily adpated to other sheets by changing strSheets = Array(1, 4). ie this code currently runs on the first and fourth sheets

    Option Explicit
   
   
   Sub KillErr()
   Dim ws As Worksheet
   Dim lRow As Long
   Dim lngCol As Long
   Dim rng1 As Range
   Dim strSheets()
   Dim strws As Variant
   strSheets = Array(1, 4)
   For Each strws In strSheets
       Set ws = Sheets(strws)
       lRow = ws.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
       lngCol = ws.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
       Application.ScreenUpdating = False
       ws.Rows(1).Insert
       Set rng1 = ws.Range(ws.Cells(1, lngCol), ws.Cells(lRow + 1, lngCol))
       With rng1.Offset(0, 1)
           .FormulaR1C1 = "=RC1=""ERR"""
           .AutoFilter Field:=1, Criteria1:="TRUE"
           .EntireRow.Delete
           On Error Resume Next
           .EntireColumn.Delete
           On Error GoTo 0
       End With
   Next
   Application.ScreenUpdating = True
   End Sub
   

1. 适用于整个 usedrange，即会处理空白

2. 通过更改可以很容易地适应其他工作表 strSheets = Array(1, 4)。即此代码当前在第一张和第四张纸上运行

    Option Explicit
   
   
   Sub KillErr()
   Dim ws As Worksheet
   Dim lRow As Long
   Dim lngCol As Long
   Dim rng1 As Range
   Dim strSheets()
   Dim strws As Variant
   strSheets = Array(1, 4)
   For Each strws In strSheets
       Set ws = Sheets(strws)
       lRow = ws.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
       lngCol = ws.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
       Application.ScreenUpdating = False
       ws.Rows(1).Insert
       Set rng1 = ws.Range(ws.Cells(1, lngCol), ws.Cells(lRow + 1, lngCol))
       With rng1.Offset(0, 1)
           .FormulaR1C1 = "=RC1=""ERR"""
           .AutoFilter Field:=1, Criteria1:="TRUE"
           .EntireRow.Delete
           On Error Resume Next
           .EntireColumn.Delete
           On Error GoTo 0
       End With
   Next
   Application.ScreenUpdating = True
   End Sub
   

回答by Reafidy

Fastest method:

最快的方法：

Sub DeleteUsingAutoFilter()

    Application.ScreenUpdating = False

    With ActiveSheet
        .AutoFilterMode = False

        .Columns("A").AutoFilter Field:=1, Criteria1:="ERR"

        .AutoFilter.Range.Offset(1, 0).EntireRow.Delete        

        .AutoFilterMode = False
    End With

    Application.ScreenUpdating = True

End Sub

Second fastest method (lots of variations to this one too):

第二快的方法（这个方法也有很多变化）：

Sub DeleteWithFind()
    Dim rFound As Range, rDelete As Range
    Dim sAddress As String

    Application.ScreenUpdating = False

    With Columns("A")
        Set rFound = .Find(What:="ERR", After:=.Resize(1, 1), SearchOrder:=xlByRows)

        If Not rFound Is Nothing Then
            Set rDelete = rFound                
            Do
                Set rDelete = Union(rDelete, rFound)
                Set rFound = .FindNext(rFound)
            Loop While rFound.Row > rDelete.Row                
        End If

        If Not rDelete Is Nothing Then rDelete.EntireRow.Delete

    End With

    Application.ScreenUpdating = True

End Sub

Autofilter method for multiple sheets:

多张纸的自动过滤方法：

Sub DeleteUsingAutoFilter()
    Dim vSheets As Variant
    Dim wsLoop As Worksheet

    Application.ScreenUpdating = False

    '// Define worksheet names here
    vSheets = Array("Sheet1", "Sheet2")

    For Each wsLoop In Sheets(vSheets)

         With wsLoop
             .AutoFilterMode = False

             .Columns("A").AutoFilter Field:=1, Criteria1:="ERR"

             .AutoFilter.Range.Offset(1, 0).EntireRow.Delete

             .AutoFilterMode = False
         End With

    Next wsLoop

    Application.ScreenUpdating = True

End Sub

回答by JDunkerley

Assuming there are always values in the cells in column A and that the data is in the first sheet, then something like this should do what you want:

假设 A 列中的单元格中总是有值并且数据在第一个工作表中，那么这样的事情应该做你想要的：

Sub deleteErrRows()
    Dim rowIdx As Integer
    rowIdx = 1

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)

    While ws.Cells(rowIdx, 1).Value <> ""
        If ws.Cells(rowIdx, 1).Value = "ERR" Then
            ws.Cells(rowIdx, 1).EntireRow.Delete
        Else
            rowIdx = rowIdx + 1
        End If
    Wend
End Sub