How To Filter Data In Excel Vba

How to Filter Data in Excel VBA

Filtering data in Excel using VBA (Visual Basic for Applications) allows you to manipulate and display specific records based on certain criteria. This can be useful when you want to analyze specific data sets or extract specific information from a larger dataset.

Example:

Let’s say you have a worksheet named “Data” with a table of sales records. The table has columns like “Customer Name,” “Product,” “Quantity,” and “Price.” You want to filter the data to display only the records where the “Product” is “Widget” and the “Quantity” is greater than 10.

  Sub FilterData()
      Dim ws As Worksheet
      Dim tbl As ListObject
      Dim rng As Range
      Dim filterCriteria As String
    
      ' Set the worksheet object
      Set ws = ThisWorkbook.Worksheets("Data")
    
      ' Set the table object (assuming your data is in a table)
      Set tbl = ws.ListObjects("Table1")
    
      ' Set the range of the table
      Set rng = tbl.Range
    
      ' Set the filter criteria
      filterCriteria = "[Product] = 'Widget' And [Quantity] > 10"
    
      ' Apply the filter
      tbl.Range.AutoFilter field:=3, Criteria1:=filterCriteria
    
      ' Optional: Copy the filtered data to a new worksheet
      tbl.Range.SpecialCells(xlCellTypeVisible).Copy Destination:=ThisWorkbook.Worksheets.Add.Range("A1")
    
      ' Turn off the filter
      tbl.AutoFilter.ShowAllData
  End Sub
  

In the above example, we first define variables for the worksheet, table, range, and filter criteria. Then, we set these variables to their corresponding objects in the Excel workbook. The filter criteria is set based on the desired conditions for the “Product” and “Quantity” columns.

We use the AutoFilter method to apply the filter to the specified range of the table. The field parameter indicates the column number (in this case, 3 for the “Quantity” column). The Criteria1 parameter specifies the filter criteria.

If you want to copy the filtered data to a new worksheet, you can use the SpecialCells(xlCellTypeVisible) method to select only the visible (filtered) cells, and then copy them to a new worksheet.

Lastly, we turn off the filter by using the ShowAllData method on the table.

By running this VBA code, the “Data” worksheet will be filtered to display only the records that meet the specified criteria (in this case, “Product” is “Widget” and “Quantity” is greater than 10). The filtered data can be copied to a new worksheet for further analysis or reporting.