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?

 

[Fluff]

Whereabouts in the sheet are the list of countries?

 

[heffo500]

Whereabouts in the sheet are the list of countries?

I have them listed on sheet1

Country Filter
Argentina Y
Brazil Y
Mexico

Thanks

 

[Fluff]

Whereabouts on the sheet?
There are 16,384 columns by 1,048,576 rows where that data could be stored.

 

[heffo500]

Whereabouts on the sheet?
There are 16,384 columns by 1,048,576 rows where that data could be stored.

Apologies:

A B C D
[TABLE="width: 285"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]CE[/TD]
[TD]Country / Market[/TD]
[TD]Apply Filter[/TD]
[TD]CE[/TD]
[/TR]
[TR]
[TD]ARG[/TD]
[TD]Argentina[/TD]
[TD]Y[/TD]
[TD]ARG[/TD]
[/TR]
[TR]
[TD]AUS[/TD]
[TD]Australia[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BRA[/TD]
[TD]Brazil[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]Canada[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CHL[/TD]
[TD]Chile[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CHN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]COL[/TD]
[TD]Colombia[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HKG[/TD]
[TD]Hong Kong[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IDN[/TD]
[TD]Indonesia[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IND[/TD]
[TD]India[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JPN[/TD]
[TD]Japan[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KOR[/TD]
[TD]Korea[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MEX[/TD]
[TD]Mexico[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MYS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NZL[/TD]
[TD]Nea Zealand[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]PER[/TD]
[TD]Peru[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]PHL[/TD]
[TD]Phillippines[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SGP[/TD]
[TD]Singapore[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]THA[/TD]
[TD]Thailand[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]TWN[/TD]
[TD]Taiwan[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]USA[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]VEN[/TD]
[TD]Venezuela[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XHK[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XHZ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XSH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]XSZ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


So the codes are located from D2:D27.

Thanks

Luke

 

[Fluff]

How about

Sub Del_Rows()
   Dim Countries() As Variant
   Dim Abc As Worksheet
   Dim cl As Range
   
   Set Abc = ThisWorkbook.Sheets("123")
   
   ReDim Countries(1 To 26 - Application.CountBlank(Sheet1.Range("D2:D27")))
   For Each cl In Sheet1.Range("D2:D27")
      If Not cl.Value = "" Then
         i = i + 1
         Countries(i) = cl.Value
      End If
   Next cl
   
   
   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

 

[heffo500]

Thanks for that I tried it but it still doesn't filter the Countries, I can see it does pull in them from the in the loop but it doesn't seem to apply that as the filter.

I have option explicit on so I set i as long.

Thanks fo the help

 

[Fluff]

Missed a bit

With Abc.UsedRange
   .AutoFilter Field:=3, Criteria1:=Countries[COLOR=#ff0000], Operator:=xlFilterValues[/COLOR]
   .AutoFilter Field:=6, Criteria1:=">=50"

 

[heffo500]

Missed a bit

With Abc.UsedRange
   .AutoFilter Field:=3, Criteria1:=Countries[COLOR=#ff0000], Operator:=xlFilterValues[/COLOR]
   .AutoFilter Field:=6, Criteria1:=">=50"

Any ideas why that still wont work?

 

[heffo500]

i got it to work however it seems to be doing the opposite to what I want:

.AutoFilter Field:=3, Criteria1:=Countries, Operator:=xlFilterValues
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter Field:=6, Criteria1:=">=50"
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete

So its deleting the rows where field 3 equals the range in sheet 1 but I want it to delete the rows whereby field 3 does not equal Countries