对象“_Worksheet"的方法“范围"失败 - 命名范围并在 VBA 中引用它 [英] Method 'Range' of object '_Worksheet' failed - Naming range and referring to it in VBA

问题描述

在单个宏中，我尝试选择一个范围、命名它，然后在自动筛选数据时引用该范围.这是我的代码:

Within a single macro, I am trying to select a range, name it, and then refer to that range when AutoFiltering data. Here's my code:

'Select and name range
Set bDataDump = ActiveWorkbook
Set DataDump = bDataDump.Sheets(1)    
DataDump.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range(Selection, Selection.End(xlDown)).Select
Set myrange = Selection

'AutoFilter that range for everything but PDC1 and delete visible rows
    'Code fails on next line
DataDump.Range("myrange").AutoFilter field:=11, Criteria1:="<>""PDC1"""
ActiveCell.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
DataDump.ShowAllData

当我运行宏时，我得到对象'_Worksheet'的方法'范围'失败"，并且调试器将第二个代码块的第一行突出显示为罪魁祸首.我无法在网络上找到任何像这样过滤数据的例子，所以我很感激任何关于我做错了什么的见解.谢谢！

I get the "Method 'Range' of object '_Worksheet' failed" when I run the macro, and the debugger highlights the first line of the second block of code as the culprit. I have not been able to find any examples on the web of filtering data like this, so I would appreciate any insight as to what I'm doing wrong. Thanks!

推荐答案

myrange 是一个范围而不是一个字符串.因此替换:

myrange is a Range and not a String. Therefore replace:

DataDump.Range("myrange").AutoFilter field:=11, Criteria1:="<>""PDC1"""

与

myrange.AutoFilter field:=11, Criteria1:="<>""PDC1"""

这篇关于对象“_Worksheet"的方法“范围"失败 - 命名范围并在 VBA 中引用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！