尝试删除行时出现运行时1004错误

Question

我希望创建一个过程，如果行包含数组中列出的任何值，该过程将删除行。

目前，我收到一个运行时1004错误：删除Range类的方法失败。我的床单没有保护，所以我不知道为什么不运行。这是失败的部分(错误在EntireRow.Delete行(：

With MonthlyRepTool.Worksheets(sheet).Rows(1)
Set b = .Find("CTM flag", LookIn:=xlValues)
End With
' filter and delete all but header row
With b
For i = 0 To 12
.AutoFilter Field:=2, Criteria1:="=" & FlagContent(i)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Next i
End With

这就是整个画面：

Sub DeleteNonHET()
Dim CTMFlag As Long

Dim FlagContent(12) As String

FlagContent(0) = "1"
FlagContent(1) = "2"
FlagContent(2) = "3"
FlagContent(3) = "4"
FlagContent(4) = "5"
FlagContent(5) = "8"
FlagContent(6) = "0"
FlagContent(7) = "#N/A"
FlagContent(8) = "G"
FlagContent(9) = "I"
FlagContent(10) = "J"
FlagContent(11) = "L"
FlagContent(12) = "M"
'Declaring year value of 1 month & 2 month
'This is important to compare datasets from 2 months ago & last month
Year_2M = Format(Date - 57, "YYYY")
'Declaring month value of 1 month & 2 month
'This is important to compare datasets from 2 months ago & last month
Month_2M = Format(Date - 57, "MM")
'This translates the current month from number to character format
MonthChar_2 = MonthName(Month_2M, False)
'opens the workbook before
'specifiying the file locations
pStr = "Z:Danny Tool Test FolderMonthly Files" & "" & Year_2M & "" & _
Month_2M & ". " & MonthChar_2 & " " & Year_2M & ""
myFile = "Dataset"
otherFile = "Monthly Reporting Tool"
sheet = "MASTERFILE_" & Year_2M & Month_2M
'checking to see that dataset wb is open, declaring it as Dataset
For Each wb In Application.Workbooks
If wb.Name Like myFile & "*" Then
Set Dataset = Workbooks(wb.Name)
End If
Next wb

'checking to see that the reporting tool is open, declaring it as MonthlyRepTool
For Each wb In Application.Workbooks
If wb.Name Like otherFile & "*" Then
Set MonthlyRepTool = Workbooks(wb.Name)
End If
Next wb

'counts number of rows in the spreadsheet
lastRow = MonthlyRepTool.Worksheets(sheet).Cells(Rows.count, "A").End(xlUp).Row
With MonthlyRepTool.Worksheets(sheet).Rows(1)
Set b = .Find("CTM flag", LookIn:=xlValues)
End With
For i = 0 To 12
' filter and delete all but header row
With b
.AutoFilter Field:=2, Criteria1:="=" & FlagContent(i)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Next i

' turn off the filters
ws.AutoFilterMode = False
End Sub

Answer 1

这是代码中有助于建立过滤范围的部分

Dim LastRow As Long
Dim LastCol As Long
Dim rng As Range
Dim ws As Worksheet
Set ws = MonthlyRepTool.Worksheets(Sheet)
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol))

With ws.Rows(1)
Set b = .Find("CTM flag", LookIn:=xlValues)
End With
For i = 0 To 12
' filter and delete all but header row
With rng
.AutoFilter Field:=b.Column, Criteria1:="=" & FlagContent(i)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Next i