Rifat Hassan
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
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.
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 iTherefore, 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).
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.
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 iTherefore, 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).
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.
