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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
Whereabouts in the sheet are the list of countries?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
Whereabouts on the sheet?
There are 16,384 columns by 1,048,576 rows where that data could be stored.
 

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
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
 

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
Missed a bit
Code:
With Abc.UsedRange
   .AutoFilter Field:=3, Criteria1:=Countries[COLOR=#ff0000], Operator:=xlFilterValues[/COLOR]
   .AutoFilter Field:=6, Criteria1:=">=50"
 

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
Missed a bit
Code:
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

New Member
Joined
Sep 28, 2016
Messages
44
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,907
Messages
5,483,657
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top