PDA

View Full Version : [SOLVED] Autofilter in Macro not working!



sofiarashid
03-14-2017, 08:15 AM
Hi

I'm hoping someone can help me.

In Sheet 1 I have created a command button. I am trying to look at sheet named "Report", go to Col AS and for any cells all the way to the bottom of the report that contain "2-WAY" I want it to delete entire rows. I have written the below;


Private Sub CommandButton21_Click()
With Worksheets("Report").Activate
With Range("A1" & Range("AS" & Rows.Count).End(xlUp))
.AutoFilter Field:=45, Criteria1:="2 - WAY"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub


I am getting error Run-time error 1004 - and the above text in red is highlighted in yellow. I have tried a few different suggestions on the net but nothing seems to work.

Also, to add that this command works if the command button is on the same sheet!

Please help!

Thanks in advance :)

mdmackillop
03-14-2017, 12:50 PM
Private Sub CommandButton21_Click()
With Worksheets("Report")
With Range(.Cells(1, 45), .Cells(Rows.Count, 45).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*2 - WAY*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter
End With
End With
End Sub

sofiarashid
03-15-2017, 01:18 AM
Private Sub CommandButton21_Click()
With Worksheets("Report")
With Range(.Cells(1, 45), .Cells(Rows.Count, 45).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*2 - WAY*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter
End With
End With
End Sub



With Range(.Cells(1, 45), .Cells(Rows.Count, 45).End(xlUp)) --> this is now highlighted yellow??


thanks

mdmackillop
03-15-2017, 02:08 AM
Can you post your workbook?

sofiarashid
03-15-2017, 02:20 AM
sorry I am unable to post the workbook as there is confidential information on it. it has header in row 1 from A-to-BC and is hundreds of rows down hence why I would like it to delete rows all the way to the bottom. Col'AS has either 2-way or 3-way in cells below but I just need 3-way. As mentioned above this command works if it is in the same tab but doesn't in a different tab. hope this helps!

Thanks

mdmackillop
03-15-2017, 02:45 AM
Here's my test book.

Because your button code is in a worksheet module, it will not operate on another worksheet module. Calling an "outside" sub gets around this.

sofiarashid
03-15-2017, 03:01 AM
thank you for this... on the code all I can see is;


Private Sub CommandButton1_Click()
Call Del2Way
End Sub

am I missing something here?

mdmackillop
03-15-2017, 03:41 AM
Del2Way is in Module1; if you right click on the word and select Definition, this takes you to the routine.

sofiarashid
03-15-2017, 04:16 AM
ah... obviously! I will try that :)

thank you!!