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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Whereabouts in the sheet are the list of countries?
 
Upvote 0
Whereabouts on the sheet?
There are 16,384 columns by 1,048,576 rows where that data could be stored.
 
Upvote 0
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
CECountry / MarketApply FilterCE
ARGArgentinaYARG
AUSAustralia
BRABrazil
CANCanada
CHLChile
CHN
COLColombia
HKGHong Kong
IDNIndonesia
INDIndia
JPNJapan
KORKorea
MEXMexico
MYS
NZLNea Zealand
PERPeru
PHLPhillippines
SGPSingapore
THAThailand
TWNTaiwan
USAUSA
VENVenezuela
XHK
XHZ
XSH
XSZ

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>


So the codes are located from D2:D27.

Thanks

Luke
 
Upvote 0
How about
Code:
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
 
Upvote 0
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
 
Upvote 0
Missed a bit
Code:
With Abc.UsedRange
   .AutoFilter Field:=3, Criteria1:=Countries[COLOR=#ff0000], Operator:=xlFilterValues[/COLOR]
   .AutoFilter Field:=6, Criteria1:=">=50"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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