How To Filter Data In Excel Using Vba

To filter data in Excel using VBA, you can use the AutoFilter method of the Range object. Here’s an example to explain the process:

  
  ' Assuming you have a worksheet named "Sheet1" with data in columns A to C
  Sub FilterData()
  
      Dim ws As Worksheet
      Set ws = ThisWorkbook.Worksheets("Sheet1")
      
      ' Specify the range containing the data
      Dim dataRange As Range
      Set dataRange = ws.Range("A1:C10")  ' Update the range as per your data
      
      ' Apply filter to column B to display only values that meet the criteria
      dataRange.AutoFilter Field:=2, Criteria1:="Value to Filter"
      
  End Sub
  
  

In the above example, we assume that you have a worksheet named “Sheet1” with data in columns A to C. You can change the worksheet name and range as per your requirement.

The AutoFilter method is used on the dataRange and takes two arguments:

• Field: Specifies the column number (relative to the range) on which the filter should be applied. In the example above, we’re using column B (which is the second column in the range).
• Criteria1: Specifies the filter criteria. Here, we’re filtering for a specific value that matches “Value to Filter” in column B.

After executing the code, the specified range will be filtered based on the criteria, and only the matching rows will be displayed. You can further customize the filter by using additional arguments and methods.

Remember to release the filter after you’re done with it. You can do this by using the AutoFilterMode property as shown below:

  
  ' Assuming you have a worksheet named "Sheet1" with filtered data
  Sub RemoveFilter()
  
      Dim ws As Worksheet
      Set ws = ThisWorkbook.Worksheets("Sheet1")
      
      ' Turn off the filter
      If ws.AutoFilterMode Then
          ws.AutoFilterMode = False
      End If
      
  End Sub
  
  

This code removes the filter from the specified worksheet (“Sheet1”). If the worksheet doesn’t have a filter applied, it won’t throw an error.