VBA Filter Issue

[heffo500]

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?

 

[heffo500]

I can change the formula on the front sheet for the above to work just wondering can I do it on the filter side

 

[Fluff]

OK, how about

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

 

[heffo500]

That worked perfectly once I added in

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

Thanks for much for the help.

 

[heffo500]

Only trouble I seem to have now is the below seems to no long apply:

.AutoFilter Field:=6, Criteria1:=">=50"

 

[Fluff]

Not sure why, as it works for me. Are you sure that the numbers are real numbers rather than text?

 

[heffo500]

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​

 

[heffo500]

Yeah those cells are set to general

 

[heffo500]

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.

 

[Fluff]

If you remove this line

.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

 

[heffo500]

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.