Excel VBA : Filter data based on drop down selection

Deepanshu Bhalla 7 Comments ,
Suppose you are asked to add a combo box (drop down) in your sheet and pull values from a range based on selection in the combo box (drop down).

How to add a combo box (drop down)

  1. Check if Developer tab is enabled.


If it is not enabled, you need to add Developer Tab. To add Developer tab into Excel 2010 and 2013, follow the instructions below:
  1. Click the File tab
  2. Click the Options tab
  3. Click the Customize Ribbon at the left
  4. At the right, select the Main Tabs from Customize The Ribbon drop down box
  5. Check the Developer item
  6. Click the OK button.

2.  On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Combo box Button.

3. Click on the cell(s) where you want the drop down to appear.

4. To display the text box, drag the left-center sizing handle to the right.

Create dummy data

Enter data into column A to C.


Task : Filter data based on column A values listed in drop down.
  1. Right click on the drop down and select Format Control and then go to Control tab
  2. In the Input range:  enter A2:A6 (values to display in the drop-down list).
  3. In the Cell link:, enter D1  (cell reference that tells you which value is selected in the drop-down list).
  4. Press ALT + F11 to open VBA editor window.
  5. Click on Insert Tab and then click Module option
  6. In the module, paste the following code
Sub FilterData()

'Get the Name of ComboBox (Drop Down)
Cbo = Application.Caller

'Get the line number of the entry and the entry data
With Sheets("Sheet1").Shapes(Cbo).ControlFormat
CboLine = .ListIndex
cboData = .List(CboLine)
End With

' turn off any autofilters that are already set
Sheets("Sheet1").AutoFilterMode = False

 ' filter column A based on combo box selection
 ActiveSheet.UsedRange.AutoFilter 1, cboData

  ' copy the visible cells to cell E8
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy Range("E8")

' Turn off any autofilters that are already set
Sheets("Sheet1").AutoFilterMode = False

End Sub

Next Step : Right Click on the drop down and click Assign Macro and select FilterData and then click OK.

Customize the above code

1. Copy visible cells from one column of a filtered range
ActiveSheet.UsedRange.AutoFilter 1, "Sandy"
rng.SpecialCells(xlCellTypeVisible).Copy

2. Customize filter range - Filter only two columns
lastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row

Set filterRange = Sheets("Sheet1").Range("A1:B" & lastRow)

' In this case we are filtering Deepanshu from column A
filterRange.AutoFilter 1, "Deepanshu"

3. Copy data only when at least one record is available after applying filter
Sheets("Sheet1").UsedRange.AutoFilter 1, cboData
Nrows=Sheets("Sheet1").Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count

If Nrows < 2 Then
MsgBox ("No record available")
Else
' Your code

4. Copy data from second row (Excluding header) 
Sheets("sheet1").UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy
Related Link - Filtering and Pasting Data to New Sheet
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

7 Responses to "Excel VBA : Filter data based on drop down selection"
  1. Great! Had been looking for something like this all over the internet...

    ReplyDelete
  2. Amazing VBA code.
    Thanks for sharing.

    ReplyDelete
  3. Thank you for this example! Is there a way to do it so that you can erase the previously copied data with the new data based on a new selection?

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This is the first & best article to make me satisfied by presenting good content. I feel so happy and delighted. Thank you so much for this article.

    ReplyDelete
Next → ← Prev