自动过滤器在另一张纸上使用VBA [英] Autofilter in another sheet using VBA

问题描述

我打算在列F中过滤以314开头的值，并清除其内容（整行）。该工作簿有三万多行，我认为在另一张表（sA）中过滤时循环不是一个好选择。我记录下面的代码如下。

I intend to filter values that begins with 314 in column F, and clear its contents(entire row). The workbook has 30,000+ rows and I think looping is not a good option when filtering in another sheet(sA). I recorded the following code below.

Sheets("sA").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AF$30436").AutoFilter Field:=6, Criteria1:="=314*" _
    , Operator:=xlAnd
ActiveCell.Offset(-181, -2).Range("A1:AF30436").Select
ActiveCell.Activate
Selection.ClearContents

当我运行代码时，出现运行时错误1004。我认为因为ActiveCell，因为我运行代码在一个不同的表（sB，其中sA的过滤表的按钮被找到）。什么可能解决这个问题？任何建议？

When I ran the code, a Runtime Error 1004 appears. I think because of the ActiveCell, because I ran the code in a different sheet(sB, where the button for filtering sheets in sA is found). What could be the possible fix to this? Any suggestions?

推荐答案

这应该工作，

Sub filter()
   Dim ws As Worksheet
   Set ws = Sheets("sheet1")

   ws.Range("$A$1:$AF$30436").AutoFilter Field:=6, Criteria1:="=314*" _
    , Operator:=xlAnd

   Dim LR As Long
   LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
   ws.Range("A2:AF" & LR).SpecialCells(xlCellTypeVisible).ClearContents
   ws.AutoFilterMode = False
End Sub

这篇关于自动过滤器在另一张纸上使用VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！