我已经做了一个小时了。我只想过滤这个表,然后删除那些已经过滤并且是可见的行,只留下那些在本质上“新”表中没有被过滤的行。
Dim ws As Worksheet
Set ws = Sheets("Score")
Set TestTable = ws.ListObjects("Score")
With ws
.Range(TestTable & "[Correct/Incorrect]").AutoFilter 6, "Correct"
.Range(TestTable & "[Correct/Incorrect]").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub
我尝试过多种方法和谷歌搜索,没有什么能接近我所需要的。
发布于 2022-09-14 08:56:27
您可以使用以下代码:
Sub deleteFilterdRows()
Dim ws As Worksheet
Set ws = Sheets("Score")
Dim lo as ListObject
Set lo = ws.ListObjects("Score")
lo.DataBodyRange.AutoFilter field:=lo.ListColumns("Correct/Incorrect").Index, Criteria1:="Correct"
Dim rgFiltered As Range
On Error Resume Next 'if nothing was found/filtered
With lo.Range
Set rgFiltered = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0
lo.AutoFilter.ShowAllData 'remove filter otherwise deletion is not possible
If Not rgFiltered Is Nothing Then
rgFiltered.Delete
End If
End Sub
首先:您以错误的方式设置了自动筛选器:当您将listobject与字符串组合时,.Range(TestTable & "[Correct/Incorrect]")
将无法工作。
其次,您需要将可见行应用到额外的范围并撤消筛选器。在筛选的列表对象中,不能删除行(在前端相同)。
发布于 2022-09-15 22:33:49
删除Excel行(ListObject)
Option Explicit
Sub DeleteListObjectRows()
' Define constants.
Const wsName As String = "Score"
Const tblName As String = "Score"
Const lcName As String = "Correct/Incorrect"
Const Criteria As String = "Correct"
' Reference the workbook, worksheet and table.
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
Application.ScreenUpdating = False
Dim vrg As Range
With tbl
' Check if the autofilter arrows are turned on.
If .ShowAutoFilter Then ' autofilter arrows are turned on
' Clear any table filters.
If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
Else ' autofilter arrows are turned off
.ShowAutoFilter = True ' turn on the autofilter arrows
End If
' Reference the criteria list column ('lc').
Dim lc As ListColumn: Set lc = .ListColumns(lcName)
' Filter the table range.
.Range.AutoFilter lc.Index, Criteria
' Attempt to reference the visible range ('vrg').
On Error Resume Next
Set vrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
' Clear the filter.
.AutoFilter.ShowAllData
End With
' Validate and delete the visible range.
If Not vrg Is Nothing Then vrg.Delete xlShiftUp
Application.ScreenUpdating = True
' Inform.
If Not vrg Is Nothing Then
MsgBox "Table criteria rows deleted.", vbInformation
Else
MsgBox "No table criteria rows found.", vbExclamation
End If
End Sub
https://stackoverflow.com/questions/73713245
复制相似问题