How to Autofilter Values Not Equal to a Certain Value with VBA in Excel

In this article, I’ll show you how you can auto-filter values that are not equal to a certain value with VBA in Excel. You’ll learn to auto-filter values from a selected range of cells, as well as from the whole worksheet.

Autofilter Values Not Equal to a Certain Value with VBA in Excel (Quick View)

Sub Autofilter_Values_from_Whole_Worksheet()

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

How to Autofilter Values Not Equal to a Certain Value with VBA in Excel: 2 Suitable Methods

Without further delay, let’s go to our main discussion today. First, we’ll auto-filter values from a whole worksheet, and then from a specific range of a worksheet.

1. Autofilter Values That are Not Equal to a Certain Value from the Whole Worksheet

First of all, we’ll learn to auto-filter the values that are not equal to a certain value from a whole worksheet with Excel VBA.

Here I’ve got a worksheet called Sheet1 that contains the names of some students, their Marks in an examination, and their Grades. The data set starts right from cell A1.

Our objective is to auto-filter the students who got a Grade not equal to F in the worksheet called Sheet2.

⧪ Step 1: Inserting the Inputs

First, you have to insert the inputs into the code. This includes the name of the source worksheet (Sheet1), the destination worksheet (Sheet2), the columns that will be filtered (1, 3), the column with a certain value (3), and the certain value (F).

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

⧪ Step 2: Autofiltering the Values with a For-loop

Next, we’ll auto-filter the values from the source worksheet to the destination worksheet by iterating through a For-loop.

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Autofilter_Values_from_Whole_Worksheet()

Source_Worksheet = "Sheet1"
Destination_Worksheet = "Sheet2"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).UsedRange.Rows.Count
    If Worksheets(Source_Worksheet).UsedRange.Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).UsedRange.Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

⧭ Output:

Change the inputs according to your need, and then run the code (First create a new worksheet called Sheet2, or anything as your destination worksheet, then run the code. Otherwise, it’ll show an error.).

You’ll get the specific columns of your data set (Columns 1 and 3 in this example) auto-filtered to the destination worksheet containing only the values that are not equal to the certain value (F in this example).

2. Autofilter Values That are Not Equal to a Certain Value from a Specific Range of Cells with Excel VBA

We’ve learned to auto-filter the values that are not equal to a certain value from a whole worksheet in Excel VBA.

Now we’ll learn to auto-filter values from a specific range.

Here I’ve got a worksheet called Sheet3 that again contains the names of some students, their Marks in an examination, and their Grades. But this time the data set starts from cell B3 up to cell D15.

This time our objective is to auto-filter the students who got a Grade not equal to F in the same worksheet, in cell F3.

⧪ Step 1: Inserting the Inputs

First, you have to insert the inputs into the code. This time it’ll include the name of the source worksheet (Sheet3), the destination worksheet (Sheet3), the destination cell (F3), the columns that will be filtered (1, 3), the column with the certain value (3), and the certain value (F).

Source_Worksheet = "Sheet3"
Source_Range = "B3:D15"

Destination_Worksheet = "Sheet3"
Destination_Cell = "F3"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

⧪ Step 2: Autofiltering the Values with a For-loop

Next, we’ll auto-filter the values by iterating through a For-loop.

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).Range(Source_Range).Rows.Count
    If Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Range(Destination_Cell).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Autofilter_Values_from_Specific_Range()

Source_Worksheet = "Sheet3"
Source_Range = "B3:D15"

Destination_Worksheet = "Sheet3"
Destination_Cell = "F3"

Dim Filtered_Columns() As Variant
Filtered_Columns = Array(1, 3)

Value_Column = 3
Value = "F"

Row_Count = 1
Column_Count = 1

For i = 1 To Worksheets(Source_Worksheet).Range(Source_Range).Rows.Count
    If Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Value_Column) <> Value Then
        For j = LBound(Filtered_Columns) To UBound(Filtered_Columns)
            Worksheets(Destination_Worksheet).Range(Destination_Cell).Cells(Row_Count, Column_Count) = Worksheets(Source_Worksheet).Range(Source_Range).Cells(i, Filtered_Columns(j))
            Column_Count = Column_Count + 1
        Next j
        Row_Count = Row_Count + 1
        Column_Count = 1
    End If
Next i

End Sub

⧭ Output:

Run the code after changing the inputs (Don’t forget to create the destination worksheet first if it’s a different one, otherwise you’ll experience errors.).

You’ll get the specific columns of your data set (Columns 1 and 3 in this example) auto-filtered to the destination cell (F3 in this example) of the destination worksheet containing only the values that are not equal to the certain value (F in this example).

Things to Remember

Before running the code to auto-filter values to a new worksheet, create that worksheet. Otherwise, you’ll experience errors.

Also, here we’ve used the UsedRange property of VBA to auto-filter values from the whole worksheet. Click it to learn about it in detail.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Autofiler Values Not Equal to a Value.xlsm

Conclusion

So, here are the ways to auto-filter values that are not equal to a certain value using VBA in Excel. Do you have any questions? Feel free to ask us. Have a nice day!