VBA Filter Issue

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
Hi

I'm trying to filter using the below:

Sub Del_Rows()


Dim Countries As String
Set wk = ThisWorkbook
Set ABC = wk.Sheets("123")


Set Countries = Sheet1.Range?????


Application.ScreenUpdating = False
With ABC.UsedRange
.AutoFilter Field:=3, Criteria1:=Countries
.AutoFilter Field:=6, Criteria1:=">=50"
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub


For field 3 I want to filter by a list of country codes which I have on my front sheet where the user has marked a y in the cell adjacent to each.

any ideas?
 
I can change the formula on the front sheet for the above to work just wondering can I do it on the filter side
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
OK, how about
Code:
Sub Del_Rows()
   Dim Countries(1 To 27) As Variant
   Dim Abc As Worksheet
   Dim cl As Range
   Dim i As Long
   
   Set Abc = ThisWorkbook.Sheets("123")
   
   For Each cl In Sheet1.Range("A2:A27")
      If Not cl.Offset(, 2).Value = "Y" Then
         i = i + 1
         Countries(i) = cl.Value
      End If
   Next cl
   
   
   Application.ScreenUpdating = False
   With Abc.UsedRange
      .AutoFilter 3, Countries, xlFilterValues
      .AutoFilter Field:=6, Criteria1:=">=50"
      .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilter
   End With
   Application.ScreenUpdating = True
End Sub
 
Upvote 0
That worked perfectly once I added in

ReDim Countries(1 To 26 - Application.CountBlank(Sheet1.Range("A2:A27")))

Thanks for much for the help.
 
Upvote 0
Only trouble I seem to have now is the below seems to no long apply:

.AutoFilter Field:=6, Criteria1:=">=50"
 
Upvote 0
Not sure why, as it works for me. Are you sure that the numbers are real numbers rather than text?
 
Upvote 0
Not sure why, as it works for me. Are you sure that the numbers are real numbers rather than text?

I have the below:

Application.ScreenUpdating = False
With ABC.UsedRange
.AutoFilter Field:=3, Criteria1:=Countries, Operator:=xlFilterValues
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter Field:=6, Criteria1:=">=50"
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

if I put it like this none of the filtering works:

Application.ScreenUpdating = False
With ABC.UsedRange
.AutoFilter Field:=3, Criteria1:=Countries, Operator:=xlFilterValues
.AutoFilter Field:=6, Criteria1:=">=50"
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
I think you may right as it will only let me filter as text. the problem I face is that in the column where I want filter greater than or equal to 50 thereis also values N.A. which I need to remain also.
 
Upvote 0
If you remove this line
Code:
.AutoFilter 3, Countries, xlFilterValues
Does it filter correctly for col 6?
Also when posting code please use code tags, the # icon in the reply window
 
Upvote 0
How I found to get it going was to do the filter for >= 50 in a separate sub and call them together in another sub so they work off a single button. It seems to do with both filters and then its doesn't recognize them. I tried commenting out that line and it didn't help I'm afraid.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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