How To Filter The Data Based On The Selection Of Cell Using VBA In Microsoft Excel

In this article, you will learn how to filter the data based on the selection of cell.

 

You need to follow the below steps:

 

  • Click on Developer tab
  • From Code group, select Visual Basic

img1

Enter the following code in the current worksheet (sheet1 in our example)

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim xcolumn As Integer

Dim xvalue As String

xcolumn = ActiveCell.Column

xvalue = ActiveCell.Value

    If Application.Intersect(ActiveCell, [headers]) Is Nothing Then

        If ActiveCell.Value<> "" Then

ActiveSheet.Range("A:d").AutoFilter Field:=xcolumn, Criteria1:=xvalue

        Cancel = True

        End If

    End If

End Sub

 

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rownumber As Integer

rownumber = ActiveCell.Row

    If Application.Intersect(ActiveCell, [headers]) Is Nothing Then

        If ActiveCell.Value<> "" Then

Range("A1:D13").Interior.ColorIndex = xlNone

Range("A" &rownumber& ":D" &rownumber).Interior.ColorIndex = 6

        End If

    End If

End Sub

img2

Note: Headers in the above code represent named range for range A1:D1

You can simply select the range A1:D1 & type headers in the Name box or you can use Defined Name in Formulas tab

img3

  • The BeforeDoubleClick event will get trigger every time the user hits double click on any cell & it will change filter down the search.
  • If we want to filter the data by Product C then, we can easily double click on cell D4 to filter the search.

img4

In case we want to filter down the search by column C & Product KFCWW then, we will double click on cell C4 to narrow down the data.

img5

  • The SelectionChange event will get activated every time the user selects any cell & it will change the color from white to yellow. If active cell is empty then the code will not run.
  • If we click on cell B3 then, the row will be highlighted in yellow color. Refer below snapshot

img6

Here, you can see the formula bar shows selected cell i.e. cell B3

In this way, you can highlight the rows with a single click & filter the data based on the selection of cell using VBA code.

Comments

  1. Used your code and it does filter, but it is filtering the column to the right of the column that I have double clicked. Ex, if i double click in cell a, the data in the b is getting filtered. Took my awhile to figure that part out, but I am unable to figure out why it is filter the adjacent column.

    • Figured it out. I don't have data in column A1, but if I set the autofilter using the menu bar to include column A it works perfectly. Now to figure out a short cut key or VBA to clear the filter.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.