首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >尝试通过以下方法删除筛选过的行时,“range类的delete方法失败”。出什么问题了?

尝试通过以下方法删除筛选过的行时,“range类的delete方法失败”。出什么问题了?
EN

Stack Overflow用户
提问于 2022-09-14 07:46:45
回答 2查看 37关注 0票数 1

我已经做了一个小时了。我只想过滤这个表,然后删除那些已经过滤并且是可见的行,只留下那些在本质上“新”表中没有被过滤的行。

代码语言:javascript
复制
    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

我尝试过多种方法和谷歌搜索,没有什么能接近我所需要的。

EN

回答 2

Stack Overflow用户

发布于 2022-09-14 08:56:27

您可以使用以下代码:

代码语言:javascript
复制
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]")将无法工作。

其次,您需要将可见行应用到额外的范围并撤消筛选器。在筛选的列表对象中,不能删除行(在前端相同)。

票数 0
EN

Stack Overflow用户

发布于 2022-09-15 22:33:49

删除Excel行(ListObject)

代码语言:javascript
复制
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
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/73713245

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档