Apply filter option in Combobox or userform

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
Good morning all,</SPAN>
Im getting a little stuck in what I see may be a simple task. </SPAN>
Firstly Ill explain what I have completed in my code so far:</SPAN>
I have created a macro that when selected runs the open file dialog box and allows a user to import a .CSV file. It then opens up a new blank Excel document, doing all the hard work sets the delimiters and pastes the selected file into A1. This also includes deleting a couple of rows and setting the work sheet name to the selected file name. ( the code below shows this….)</SPAN>


Code:
Option Explicit</SPAN>
Sub opencsv()</SPAN>
' Macro to download/upload to select a file</SPAN>
' Created on 24/02/2016</SPAN>
Dim myFile As String                                                      ' Declare variables.</SPAN>
Dim FilterIndex As Integer</SPAN>
Dim Title As String</SPAN>
Dim FileName As Variant</SPAN>
 
Dim myUserForm As UserForm1</SPAN>
myFile = "CSV Files (*.csv),*.csv"                                        ' Select CSV file.</SPAN>
Title = "Select .CSV File to Import"                                      ' Open dialog box title.</SPAN>
FileName = Application.GetOpenFilename(FileFilter:=myFile, Title:=Title)                                                                         </SPAN>
                                                                      
If FileName = False Then                                                  ' Select and open the dialog box.</SPAN>
MsgBox "No File Selected"                                                 ' If no file selected open dialog box.</SPAN>
Exit Sub</SPAN>
End If</SPAN>
' Set delimiters to FileName</SPAN>
Workbooks.OpenText FileName:=FileName, DataType:=xlDelimited, Comma:=True, Local:=True</SPAN>
 
ActiveSheet.Range("D:D,E:E,F:F,I:I").EntireColumn.Delete                   ' Delete selected columns</SPAN>
 '.Range(.Cells(4, tblCol + 4), .Cells(5, tblCol + 5)).EntireColumn.Delete ' Delete entire range of columns</SPAN>
 
Range("A1").Select</SPAN>
    Selection.AutoFilter</SPAN>
Set myUserForm = New UserForm1</SPAN>
  myUserForm.Show</SPAN>
End Sub</SPAN>


My next step I am having trouble getting my head around and appreciate any help on this. I am not sure if this is the correct approach. Im wanting to include a user interface that allows a user to select a particular column using a combobox, which filters all the other data. </SPAN>
Test data:</SPAN>


Tail</SPAN></SPAN>
Type_Ref</SPAN></SPAN>
Datetime</SPAN></SPAN>
Sys_Ref</SPAN></SPAN>
Reg_Ref</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
59:41.8</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground 97%</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
13:43.3</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground 97%</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground 97%</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground Max</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
120 Kts</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
120 Kts</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
140 Kts</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
59:41.8</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground 97%</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
13:43.3</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground 97%</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground 97%</SPAN></SPAN>
M77K32</SPAN></SPAN>
FIXEDAC Mk100</SPAN></SPAN>
40:37.7</SPAN></SPAN>
FIXEDAC Mk100 Engines</SPAN></SPAN>
Ground Max</SPAN></SPAN>




If the combobox is the correct way to go then the next step is be able to select ‘ Reg_Ref’ column, select ‘Ground 97%’ apply the filter to the selected data and paste data into new work sheet. The data may vary from 1000 rows to 90 000 rows.

From there I will be able to create the other filters in other columns ie Tail...select M77K39..apply filter to rest of data....etc

Any help with this will be much appreciated. </SPAN>



<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
Not usre what the test data has been added like above:

Hope this is a better example:

Tail
Type_Ref
Datetime
Sys_Ref
Reg_Ref
M77K32
FIXEDAC Mk100
59:41.8
FIXEDAC Mk100 Engines
Ground 97%
M77K32
FIXEDAC Mk100
13:43.3
FIXEDAC Mk100 Engines
Ground 97%
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
Ground 97%
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
Ground Max
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
120 Kts
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
120 Kts
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
140 Kts
M77K32
FIXEDAC Mk100
59:41.8
FIXEDAC Mk100 Engines
Ground 97%
M77K32
FIXEDAC Mk100
13:43.3
FIXEDAC Mk100 Engines
Ground 97%
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
Ground 97%
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
Ground Max
M77K32
FIXEDAC Mk100
40:37.7
FIXEDAC Mk100 Engines
120 Kts

<tbody>
</tbody>
 

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
Still cant get my head around this one, not sure where to start. Appreciate a little guidance on this one....thanks in advance.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top