vba auto filter greater than / less than 0

Question

I want to filter those that are positive numbers, remove filter, then filter the negative ones, then remove filter again.

I use this code, this doesn't work.

Selection.AutoFilter Field:=7, Criteria1:=">0"
Selection.AutoFilter Field:=7, Criteria1:="<0"

Could you please help?

Thank you!

Answer 1

This will:

1. Check to see if a filter is already applied. if not, turn on filter
2. Apply filter 1 (>0)
3. Remove filter
4. Apply filter 2 (<0)
5. Remove filter

The code, as is, is just applying the filters and then turning them off. This means you will not see anything happen when you run the code. If you step through the code using (F8), and remove the ScreenUpdating = False, you will see everything happen on your sheet.

---

Option Explicit

Sub Filter()
Application.ScreenUpdating = False

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") 'Put your sheet name inside the quotes

With ws
  If Not .AutoFilterMode Then .Range("G1").AutoFilter

     'First Critiera
     .Range("G1").AutoFilter Field:=7, Criteria1:=">0"
         'Do what you want with your filtered data here
     .AutoFilterMode = False

     'Second Criteria
     .Range("G1").AutoFilter Field:=7, Criteria1:="<0"
         'Do what you want with your filtered data here
     .AutoFilterMode = False
End With

Application.ScreenUpdating = True
End Sub