Results 1 to 8 of 8

AutoFilter Method of Range Class Failed

This is a discussion on AutoFilter Method of Range Class Failed within the Excel Questions forums, part of the Question Forums category; I have the following code that is looking for cells with 39 in them and then cutting and pasting those ...

  1. #1
    New Member
    Join Date
    Jun 2004
    Posts
    35

    Default AutoFilter Method of Range Class Failed

    I have the following code that is looking for cells with 39 in them and then cutting and pasting those rows into a different sheet. The full data is in Col A through Col R. I'm looking for cells in Col R with 39.

    I have this in my Personal.xls file. The first time I testing this it worked. The AutoFilter Method of Range Class Failed error occurs at the line in red.

    Sub RemPurple()

    Sheets("Add On & Opex").Select

    LastRow = Sheets("Add On & Opex").Cells(Rows.Count, "D").End(xlUp).Row

    Range("R5").AutoFilter Field:=18, Criteria1:="39"
    Range("A5:R" & LastRow).SpecialCells(xlCellTypeVisible).Cut
    Sheets("L3 Closed").Select
    lastrow2 = Sheets("L3 Closed").Cells(Rows.Count, "D").End(xlUp).Row + 1
    Rows(lastrow2).Select
    ActiveSheet.Paste
    Sheets("Add On & Opex").Select
    Selection.AutoFilter Field:=1
    Selection.AutoFilter

    End Sub

    Please tell me what I'm doing wrong. I'm a novice to VBA.

    Any help would be greatly appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default

    Hi

    I think (altho I may be wrong) that the code will only work if autofilter is already applied to the range A5:R5 - as in this case the R column is field 18. However, if autofilter has not been applied, then given the way you are passing the autofilter method, the R column is field 1 hence the problem.

    A possible solution therefore if to include the following line before your problem line, and then modify the problem line:

    Code:
    ...
    Application.AutofilterMode = False
    Range("R5").AutoFilter Field:=1, Criteria1:="39"
    ...rest of code...
    Give it a try, and post back if still no joy.
    Richard Schollar

    Using xl2013

  3. #3
    New Member
    Join Date
    Jun 2004
    Posts
    35

    Default

    No, unfortunately that didn't work. I received an error: Run-Time Error '438': Object doesn't support this property or method.

    I searched for that error and found a post that said to enter:

    ActiveWindow.ActiveSheet.AutoFilterMode = False

    I tried that and received: "The command you chose cannot be performed with multiple selections. Select a single range and click the command again."

    I'm still searching. Do you have any other ideas?

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default

    Sorry - that was my error. Code should have been:

    Code:
    ...
    ActiveSheet.AutofilterMode = False
    Range("R5").AutoFilter Field:=1, Criteria1:="39"
    ...rest of code...
    The macro should be activated when the relevant sheet is the active one.
    Richard Schollar

    Using xl2013

  5. #5
    New Member
    Join Date
    Jun 2004
    Posts
    35

    Default

    Got it! Thank you!!!

    I didn't have a Column Header for that column which was throwing things off. Between figuring that out and your assistance, it worked like a charm EXCEPT when the last row of data is 39 and needs to be moved to the new sheet. Then I get this message again: "The command you chose cannot be performed with multiple selections. Select a single range and click the command again."

    This is the code it doesn't like:

    Range("A5:R" & LastRow).SpecialCells(xlCellTypeVisible).Cut

    Thanks again for your help.

  6. #6
    New Member
    Join Date
    Jun 2004
    Posts
    35

    Default

    Finally got it working. If I sort the data first and then run the macro the code works. I don't know if that is the right way to do it but for now it works!!!!

    Richard, Thanks for your help. I do appreciate it.

  7. #7
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default

    how about?
    Code:
    With Range("R5:R" & LastRow).SpecialCells(12)
         .Copy Sheets("L3 Closed").Cells(Rows.Count,"d").End(xlUp).Offet(1)
         .EntireRow.Delete
    End With

  8. #8
    New Member
    Join Date
    Jun 2004
    Posts
    35

    Default

    That works perfectly!! Thank you!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com