基于条件VBA删除表行 [英] Delete Table Row Based on Criteria VBA

问题描述

我正在尝试基于两列中的值删除特定的表行.我试图将过滤器应用于表列以缩小条件，但是单击删除后，整个行将被删除，从而导致表外的值被删除.另外，宏录制器并不像我希望的那样动态，因为它仅选择我在录制时单击的单元格.

I am attempting to delete a specific table row based on values in two columns. I attempted to apply filters to the table columns to narrow my criteria, but once I click delete, the ENTIRE ROW is deleted causing values outside of the table to be deleted. Also, the macro recorder isn't as dynamic as I'd like it to be, since it ONLY selects the cell I clicked while recording.

   Sub Macro2()
    '
    ' Macro2 Macro
    '
    '
         ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
    "Apple"                               \\Narrowing criteria in Column 1 of the table
         Range("A4").Select               \\This only applies to a specific cell, and the value can shift
         Selection.EntireRow.Delete       \\This will delete the entire sheet row, I'd like for only the table row to be deleted    
         Range("A5").Select
         Selection.EntireRow.Delete
         Selection.EntireRow.Delete
    End Sub

是否存在一种方法，可以在满足条件的列中找到所需的字符串并仅删除表中的行?我试图仅删除ListObject.ListRows，但它仅引用我选择的行，而不引用基于条件的行.

Is there a way to find the desired string in a column and delete only the rows in the table once the criteria is met? I attempted to only delete the ListObject.ListRows, but it only references the row I've selected, and not the one based off criteria.

推荐答案

您可以使用.DataBodyRange和.SpecialCells(xlCellTypeVisible)将范围变量设置为等于过滤范围，然后取消过滤并删除:

You could use .DataBodyRange and .SpecialCells(xlCellTypeVisible) to set a range variable equal to the filtered ranges, then unfilter and delete:

Dim dRng As Range
With ActiveSheet.ListObjects("Table1")
    .Range.AutoFilter Field:=1, Criteria1:="Apple"
    If WorksheetFunction.Subtotal(2, .DataBodyRange) > 0 Then
        Set dRng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        .Range.AutoFilter
        dRng.Delete xlUp
    End If
End With

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