Excel ListObject表-从ListObject表中删除过滤/隐藏的行 [英] Excel ListObject Table - Remove filtered / hidden rows from ListObject table

问题描述

我正在努力寻找一种方法，以从ListObject表中删除过滤/隐藏的行.

I am banging my head to find a way to delete filtered/hidden rows from a ListObject table.

过滤不是通过代码执行的，而是由用户使用表头过滤器执行的.我想在不列出ListObject表并执行小计操作之前删除筛选/隐藏的行.如果我在取消列出表之前不删除已过滤/隐藏的行，则这些行会重新出现.

The filtering is not performed trough the code, it's performed by the user using the table header filters. I want to remove the filtered/hidden rows before unlisting the ListObject Table and perform Subtotal operation. If I don't delete the filtered/hidden rows before unlisting the Table, these rows reappear.

当前代码:

Sub SubTotalParClassification(ReportSheetTitle)
Dim ws As Worksheet
Dim drng As Range

Endcol = ColCalculationEndIndex
Set ws = Sheets(ReportSheetTitle)

'CODE TO REMOVE HIDDEN/FILTERED ROWS
Set lo = ActiveSheet.ListObjects("Entrée")
For i = 1 To lo.ListRows.Count Step 1
    If Rows(lo.ListRows(i).Range.Row).Hidden = True Then
        lo.ListRows(i).Delete
Next

' convert the table back to a range to be able to build subtotal
ws.ListObjects("Entrée").Unlist
With ws
    'Select range to Subtotal
    Set drng = .Range(.Cells(REPORTHEADERROW, REPORTSSTARTCOL),     .Cells(EndRow, Endcol))

    'apply Excel SubTotal function
    .Cells.RemoveSubtotal
    drng.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(Endcol - 6,   Endcol - 5, Endcol - 4, Endcol - 3, Endcol - 2, Endcol - 1)
     End With

'Update EndRow
EndRow = ActiveSheet.Cells(Rows.Count, REPORTSSTARTCOL).End(xlUp).Row
End Sub

推荐答案

不幸的是， xlCellTypeVisible .要收集所有隐藏的行，我们需要找到.DataBodyRange属性和可见行，而不是

Unfortunately, the Range.SpecialCells method does not have a specific parameter for xlCellTypeInvisible, only one for xlCellTypeVisible. To collect all of the hidden rows we need to find the compliment of the .DataBodyRange property and the visible rows, not the Intersect. A short UDF can take care of that.

一旦建立了联盟的隐藏行您不能简单地删除行；您必须循环浏览 Range.Areas属性.每个区域将包含一个或多个连续的行，可以将其删除.

Once a Union of the hidden rows have been established you cannot simply delete the rows; you must cycle through the Range.Areas property. Each area will contain one or more contiguous rows and those can be deleted.

Option Explicit

Sub wqewret()
    SubTotalParClassification "Sheet3"
End Sub

Sub SubTotalParClassification(ReportSheetTitle)
    Dim a As Long, delrng As Range
    With Worksheets(ReportSheetTitle)
        With .ListObjects("Entrée")
            'get the compliment of databody range and visible cells
            Set delrng = complimentRange(.DataBodyRange, .DataBodyRange.SpecialCells(xlCellTypeVisible))
            Debug.Print delrng.Address(0, 0)
            'got the invisible cells, loop through the areas backwards to delete
            For a = delrng.Areas.Count To 1 Step -1
                delrng.Areas(a).EntireRow.Delete
            Next a
        End With
    End With
End Sub

Function complimentRange(bdyrng As Range, visrng As Range)
    Dim rng As Range, invisrng As Range

    For Each rng In bdyrng.Columns(1).Cells
        If Intersect(visrng, rng) Is Nothing Then
            If invisrng Is Nothing Then
                Set invisrng = rng
            Else
                Set invisrng = Union(invisrng, rng)
            End If
        End If
    Next rng
    Set complimentRange = invisrng
End Function

请记住，删除行时从底部开始并朝顶部开始被认为是最佳实践".

Remember that it is considered 'best practise' to start at the bottom and work towards the top when deleting rows.

这篇关于Excel ListObject表-从ListObject表中删除过滤/隐藏的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！