0

I want to filter all "X" in column F in the range A:F using Excel VBA. Here is the part of code. After selecting the autofilter it gives me a Run-time error '1004' - Method 'Range' of object'_Global' failed. Help me out:

Function Advanced_Filter()

    Sheets("Email").Range("M1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "X"
    Range("A2:F2").Select
    Selection.AutoFilter
    Range("A2:F244").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "Email!Criteria"), CopyToRange:=Range("M3"), Unique:=False

This how the sheet looks like

PratikSharma
  • 321
  • 2
  • 17
  • 1
    What scope are you working in? In order to call `Range` without a qualifier, you must be within a worksheet's scope (where children of worksheet objects are implicitly qualified by the scoped sheet). Otherwise you must define on which worksheet you want to get that range. – TFrazee May 18 '18 at 07:19
  • Sheet is "Email" and the entire macro runs on that sheet. so defining the sheet is not a problem. as said, I'm getting runtime error in the last line of the code. – PratikSharma May 18 '18 at 07:21
  • 1
    The Range object needs an address or bounding cells passed in as a parameter. `Email!Criteria` is not a valid address. Change "Criteria" to the address of your criteria on the Email worksheet. – TFrazee May 18 '18 at 07:37
  • 1
    While I'm hesitant to provide a bandaid with no parent worksheet reference, `CriteriaRange:=Range("Criteria")` should temporarily resolve your issue. [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) –  May 18 '18 at 07:42
  • @greenarrow, any news? – DisplayName May 18 '18 at 10:14
  • no I tried changing the `Email!Criteria` to `Criteria`. didn't work that way. can't figure out what is the issue. can anyone suggest any alternative way out? – PratikSharma May 18 '18 at 10:31
  • Tried this too `Range("A2:F244").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Email").Range("Criteria"), CopyToRange:=Range("M3"), Unique:=False` didn't work out. – PratikSharma May 18 '18 at 10:33

0 Answers0