VBA Filter Issue

heffo500

New Member
Joined
Sep 28, 2016
Messages
44
I can change the formula on the front sheet for the above to work just wondering can I do it on the filter side
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

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

heffo500

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

New Member
Joined
Sep 28, 2016
Messages
44
Only trouble I seem to have now is the below seems to no long apply:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Not sure why, as it works for me. Are you sure that the numbers are real numbers rather than text?
 

heffo500

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

New Member
Joined
Sep 28, 2016
Messages
44
Yeah those cells are set to general
 

heffo500

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

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

heffo500

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

Watch MrExcel Video

Forum statistics

Threads
1,099,134
Messages
5,466,864
Members
406,506
Latest member
Patb3

This Week's Hot Topics

Top