范围类的删除方法失败

Question

我有一些代码，我正在使用索引（匹配）基于单元格与下拉菜单。当用户选择某种安全性时，会输出一个CUSIP，然后粘贴来自bloomberg的公式以将数据输出到excel中。

然后我继续创建一个表，但想过滤使用自动筛选器的表，并删除不满足过滤标准的行，但似乎没有工作出于某种原因！我还插入了一个ActiveX控件窗体按钮，这样当用户双击下拉菜单时，他们可以搜索安全性并自动完成。

请帮忙，谢谢！

Sub INDEX_MATCH_CUSIP_TO_SHORTDESCRIPTION() 

Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 

Sheets("Sheet4").Range("B3:E100").Delete 

Range("B2").Select 
test = Application.WorksheetFunction.Index(Sheets("DEX Spread Report (Corp)").Range("B7:B1600"),   Application.WorksheetFunction.Match(ActiveCell.Value, Sheets("DEX Spread Report (Corp)").Range("D7:D1600"), 0), 1) 
ActiveCell.Offset(1, 0).Value = test 

Application.ScreenUpdating = True 
Application.Calculation = xlCalculationAutomatic 

End Sub 

Sub BBRG_FORMULAS_FOR_SECURITY() 

Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 

Dim CUSIPS As String 

    Sheets("Sheet4").Select 
    Range("B2").Select 

CUSIPS = ActiveCell.Offset(1, 0).Value 

ActiveCell.Offset(2, 0).Value = "=BDS(""" & CUSIPS & """ & ""& CUSIP"",""ALL_HOLDERS_PUBLIC_FILINGS"", ""STARTCOL=1"", ""ENDCOL=1"")" 
ActiveCell.Offset(2, 1).Value = "=BDS(""" & CUSIPS & """ & ""& CUSIP"",""ALL_HOLDERS_PUBLIC_FILINGS"", ""STARTCOL=6"", ""ENDCOL=8"")" 


Application.ScreenUpdating = True 
Application.Calculation = xlCalculationAutomatic 


End Sub 


Sub Create_Table_and_AutoFilter() 

Dim wksDest As Worksheet 
Dim LastRow As Long 
Dim rng  As Range 
Dim rngDelete As Range 

Sheets("Sheet4").Select 
Set wksDest = Worksheets("Sheet4") 

LastRow = Cells(Rows.Count, 2).End(xlUp).row 
LastRow1 = Cells(Rows.Count, 2).End(xlUp).row 

ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(4, 2), Cells(LastRow, 5)), , xlYes).Name = "HoldersTable" 

With wksDest 
Set rng = Range(Cells(4, 2), Cells(LastRow1, 5)) 
rng.AutoFilter Field:=1, Criteria1:="<=1000" 
Set rngDelete = rng.SpecialCells(xlCellTypeVisible) 
rng.AutoFilter 
rngDelete.EntireRow.Delete 
End With 

End Sub 

Answer 1

你最有可能试图删除表头

尝试从With wksDest的代码与下面的代码段代入End With：

With wksDest.Range(Cells(4, 2), Cells(LastRow1, 5)) 
    .AutoFilter Field:=1, Criteria1:="<=1000" 
    If Application.WorksheetFunction.Subtotal(103, .Cells.Resize(,1)) > 1 Then .offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    .AutoFilter 
End With