VBA-应用过滤器后删除可见行 [英] VBA - Delete Visible Rows After Filter Applied

问题描述

我有两个宏，它们按日期过滤工作表列，然后删除不相关的列.

I have the two Macro's which filters a worksheet column by date and then delete's the irrelevant columns.

但是，这是非常多的错误，我需要一些帮助来纠正它.

However, it is very buggy and I need some help to correct that please.

这是第二个宏应该执行的操作:

This is what the second Macro is supposed to do:

1)应用过滤器后，仅删除可见行，但第一行(标题)除外-目前，即使我有在我的代码中起作用.

1) Simply delete the visible rows after the filter is applied, except the first row (headers) - Currently, it deletes ALL visible rows including the first row, even though I have the Offset function in my code.

2)删除所有过滤器-现在可以正常使用

2) Remove all filters - This is working fine now

Sub DeleteVisibleRows()

Dim ws1 As Worksheet
Dim WorkRng As Range

Set ws1 = ActiveWorkbook.Sheets("Consolidated")

On Error Resume Next

Set WorkRng = Application.Selection

Application.ScreenUpdating = False
With ws1

WorkRng.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete

ws1.AutoFilterMode = False

End With

Application.ScreenUpdating = True
End Sub

推荐答案

SpecialCells的工作方式与之前的答案相同.

SpecialCells will work like the previous answer.

    Sub Button1_Click()
    Dim sh As Worksheet, rng As Range, LstRw As Long

    Set sh = Sheets("Sheet1")
    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LstRw).SpecialCells(xlCellTypeVisible)
        rng.EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

这篇关于VBA-应用过滤器后删除可见行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！