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

In this article, I’ll show you how you can autofilter values that are not equal to a certain value with VBA in Excel. You’ll learn to autofilter 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

VBA Code to Autofilter Values Not Equal to a Certain Value in Excel Value


Download Practice Workbook

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


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

Without further delay, let’s go to our main discussion today. First, we’ll autofilter 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 autofilter 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 grade. The data set starts right from cell A1.

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

Our objective is to autofilter 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 to 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 the 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 autofilter 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

VBA Code to Autofilter Values Not Equal to a Certain Value in Excel Value

⧭ 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).

Read More: VBA to AutoFilter with Multiple Criteria on Same Field in Excel (4 Methods)


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

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

Now we’ll learn to autofilter 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 grade. But this time the data set starts from cell B3 up to cell D15.

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

This time our objective is to autofilter 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 to 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 autofilter 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

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

⧭ 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).

Read More: [Fix]: AutoFilter Method of Range Class Failed (5 Solutions)


Things to Remember

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

Also, here we’ve used the UsedRange property of VBA to autofilter values from the whole worksheet. Click it to know about it in detail.


Conclusion

So, here are the ways to autofilter values that are not equal to a certain value using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo