excel 用于使用带有标题名称的自动筛选清除空白数据的宏

vh0rcniy 于 2023-02-14 发布在其他

关注(0)|答案(2)|浏览(118)

我试图使一个宏，以清除数据集通过删除空白单元格使用自动过滤方法与标题，而不是列号。正如你所看到的。有没有在这个宏的单元格编号，也不会有。一切都必须是自动的。这是想法。
我写了90%的代码。我到达了水源，但我不能喝。
我得到了最后一行的错误。
错误1004：Range类的AutoFilter方法失败。
下面是代码：

Sub DeleteBlank()

Dim WrkS As Worksheet, LsC As Range, FsC As Range, Tab As Range
Dim LsH As Range, RNbr As Long, CNbr As Long, HdrRow As Range, FltCol As Variant

Set WrkS = Worksheets("data")

' Last cells 

Set LsC = Cells(Cells.Find(what:="*", SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).row, _
  Cells.Find(what:="*", SearchOrder:=xlByColumns, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

' First cells 

Set FsC = Cells(Cells.Find(what:="*", after:=LastCell, SearchOrder:=xlRows, _
  SearchDirection:=xlNext, LookIn:=xlValues).row, _
  Cells.Find(what:="*", after:=LastCell, SearchOrder:=xlByColumns, _
  SearchDirection:=xlNext, LookIn:=xlValues).Column)

FsC.Activate

RNbr = ActiveCell.row

LsC.Activate

CNbr = ActiveCell.Column

'to set the last header 

Set LsH = Cells(RNbr, CNbr)

' to set the header Row

Set HdrRow = Range(FsC, LsH)

Set Tab = WrkS.UsedRAnge  

' to get the Column name in which I have to delete all blank 

With HdrRow

FltCol = .Find(what:="name", LookAt:=xlWhole).Column

End With

' the problem is below 
' Error 1004: AutoFilter method of Range class failed.
WrkS.Tab.AutoFilter Field:=FltCol, Criteria1:="="

End Sub

excel

来源：https://stackoverflow.com/questions/50027672/macro-to-clean-data-from-blank-using-autofilter-with-headers-name

2条答案

按热度按时间

xxhby3vn1#

你能试试这个吗？我不能声明一个叫做“Tab”的变量。因为它已经被定义为WrKS上的一个范围，你不需要AF行上的工作表引用。另外，当使用查找最佳值来检查值是否被找到以避免错误。你真的应该在任何地方使用工作表引用（或者在开始时激活工作表）。

Sub DeleteBlank()

Dim WrkS As Worksheet, LsC As Range, FsC As Range, Tab1 As Range
Dim LsH As Range, RNbr As Long, CNbr As Long, HdrRow As Range, FltCol As Variant

Set WrkS = Worksheets("data")

Set LsC = Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues)
Set FsC = Cells.Find(what:="*", after:=LastCell, SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues)

If Not FsC Is Nothing Then
    If Not LsC Is Nothing Then
        RNbr = FsC.Row
        CNbr = LsC.Column
        Set LsH = Cells(RNbr, CNbr)
        Set HdrRow = Range(FsC, LsH)
        Set Tab1 = WrkS.UsedRange
        FltCol = HdrRow.Find(what:="name", LookAt:=xlWhole).Column
        Tab1.AutoFilter Field:=FltCol, Criteria1:="="
    End If
End If

End Sub

赞(0）回复(0）举报 2023-02-14

gzszwxb42#

也许你可以把它缩短成这样

Option Explicit

Sub DeleteBlank()

    With Worksheets("data").UsedRange ' reference relevant worksheet "usedrange"
        With Intersect(.Rows(1).Find(what:="name", LookAt:=xlWhole).EntireColumn, .Cells) 'reference its column whose top cell content is "name"
            .AutoFilter Field:=1, Criteria1:="=" 'filter referenced column blank cells
            If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' if any filtered cells other than first row (header) then delete their entire row
        End With
        .Parent.AutoFilterMode = False
    End With

End Sub

赞(0）回复(0）举报 2023-02-14

我来回答

excel 用于使用带有标题名称的自动筛选清除空白数据的宏

2条答案

相关问题

热门标签

最新问答