AutoFilter Method of Range Class Failed

densac

New Member
Joined
Jun 23, 2004
Messages
35
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top