how to get the filter dropdown list in excel

Question

I am writing a program that can read a spreadsheet(part catalog) of data, and put the data in usable columns in a different spreadsheet. There are 750,000 rows in the part catalog. I would like to use a dropdown of each unique value at the beginning of the program. In a spreadsheet, you can put a filter on a column. If you click the dropdown of that filter, it provides a list of unique values(See picture below). How can I access that list?

filter list

I have tried recreating the list by looking at each value(checking if its already in the list) and adding it. But It's been running for 4+hours now and still not finished. I need something much faster.

The other option is to create a static list manually. I don't mind this, but just like the peace of mind that I'm getting info from the data rather than a static list of assumptions.

What do you think?

Public Class Catalog
    Public XlApp As Excel.Application
    Public Xlbook As Excel.Workbook
    Public XlSh As Excel.Worksheet
    Dim WrdApp As Word.Application
    Dim WrdDoc As Word.Document
    Public SpreadsheetHeaderRowIndex As Integer
    Public TypeSelectionList As List(Of String)


    Private Sub Catalog_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        OpenSpreadsheet()

        GetFilterList()

        Dim TypeForm As New Type_Selection
        TypeForm.ShowDialog()

        ApplyFiltertoSpreadsheet()

    End Sub

#Region " OpenSpreadsheet | Private Sub"
    Private Sub OpenSpreadsheet()

        Dim Directry As String = "file location - part catalog\"
        Dim FileName As String = "part catalog records with usage by plants v2.xlsx"

        Try
            ' If excel is already open, get the application.
            XlApp = SysInter.Marshal.GetActiveObject("Excel.Application")
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Found the excel application.")

            ' Look through the open workbooks for the one being opened and get it.
            For Each XlObject As Excel.Workbook In XlApp.Application.Workbooks : If XlObject.Name = FileName Then : Xlbook = XlObject : DBWrite.WriteLine("Catalog | OpenSpreadsheet | Found spreadsheet: " & FileName) : Exit For : End If : Next

            ' If the workbook being opened isn't already opened, then open it.
            If Xlbook Is Nothing Then : Xlbook = XlApp.Workbooks.Open(Directry & FileName) : DBWrite.WriteLine("Catalog | OpenSpreadsheet | Opening spreadsheet: " & FileName) : End If

            ' Bring the excel into focus.
            Xlbook.Activate()
            XlApp.Visible = True
            XlApp.WindowState = Excel.XlWindowState.xlMaximized
            XlSh = Xlbook.Sheets("Select part_catalog")
            XlSh.Activate()
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Spreadsheet is active and visible.")

        Catch ex As NullReferenceException
            ' If excel is not open.
            'create a new excel
            XlApp = CreateObject("Excel.Application")
            XlApp.Visible = True
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Starting a new excel application.")

            'open the existing report
            Xlbook = XlApp.Workbooks.Open(Directry & FileName)
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Opening spreadsheet: " & FileName)

            ' Wait
            Do Until Xlbook.ReadOnly = False : XlApp.Wait(TimeValue("00:00:01")) : Loop

            ' Bring the excel into focus.
            Xlbook.Activate()
            XlApp.Visible = True
            XlApp.WindowState = Excel.XlWindowState.xlMaximized
            XlSh = Xlbook.Sheets("Select part_catalog")
            XlSh.Activate()
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Spreadsheet is active and visible.")

        Catch ex As System.Runtime.InteropServices.COMException
            ' If excel is not open.
            'create a new excel
            XlApp = CreateObject("Excel.Application")
            XlApp.Visible = True
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Starting a new excel application.")

            'open the existing report
            Xlbook = XlApp.Workbooks.Open(Directry & FileName)
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Opening spreadsheet: " & FileName)

            ' Wait
            Do Until Xlbook.ReadOnly = False : XlApp.Wait(TimeValue("00:00:01")) : Loop

            ' Bring the excel into focus.
            Xlbook.Activate()
            XlApp.Visible = True
            XlApp.WindowState = Excel.XlWindowState.xlMaximized
            XlSh = Xlbook.Sheets("Select part_catalog")
            XlSh.Activate()
            DBWrite.WriteLine("Catalog | OpenSpreadsheet | Spreadsheet is active and visible.")

        End Try

    End Sub
#End Region

#Region " GetFilterList | Private Sub"
    Private Sub GetFilterList()
        Try

            ' Static list from reading the spreadsheet
            TypeSelectionList = New List(Of String) From {GET FILTER LIST}
            DBWrite.WriteLine("Catalog | GetFilterList | Established the filter list.")

        Catch ex As Exception
            MessageBox.Show("Catalog | GetFilterList" & vbNewLine & "Problems creating the filter list.", "Error!")
        End Try
    End Sub
#End Region

#Region " ApplyFiltertoSpreadsheet | Private Sub"
    Private Sub ApplyFiltertoSpreadsheet()
        'Try
        'If XlSh.FilterMode Then : DBWrite.WriteLine("Catalog | ApplyFiltertoSpreadsheet | Filtermode is ON.") : Else : DBWrite.WriteLine("Catalog | ApplyFiltertoSpreadsheet | Filtermode is OFF.") : End If

        ' Static list from reading the spreadsheet
        Dim LastRowIndex As Integer = XlSh.UsedRange.Rows.Count

        XlSh.Range("F13", "F" & LastRowIndex).AutoFilter(1, Type_Selection.FilterText, Excel.XlAutoFilterOperator.xlAnd,, True)

        Dim FcolFilter As Excel.Filter = XlSh.AutoFilter.Filters.Item(5)
        FcolFilter.Criteria1()
        Stop
        'Dim ListOfFilters As New List(Of Excel.Filter) : For Each Filtr As Excel.Filter In XlSh.AutoFilter.Filters : ListOfFilters.Add(Filtr) : Next
        'Stop
        'If XlSh.FilterMode Then : DBWrite.WriteLine("Catalog | ApplyFiltertoSpreadsheet | Filtermode is ON.") : Else : DBWrite.WriteLine("Catalog | ApplyFiltertoSpreadsheet | Filtermode is OFF.") : End If

        'Catch ex As Exception
        '    MessageBox.Show("Catalog | GetFilterList" & vbNewLine & "Problems applying the selected filter.", "Error!")
        'End Try
    End Sub
#End Region

End Class

**Popup dialog class
Public Class Type_Selection
    Public FilterText As String
    Dim g_FormCatSelect As Type_Selection = Me

    Private Sub Type_Selection_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        LoadFilterItems()

    End Sub

    Private Sub Btn_Select_Click(sender As Object, e As EventArgs) Handles Btn_Select.Click
        FilterText = Me.CmboBox_SelectAType.SelectedItem
        Me.Close()
    End Sub

#Region "Load combobox with filter items"
    Private Sub LoadFilterItems()
        ' Add the categories to the dropdown list.
        For Each Entity As String In Catalog.TypeSelectionList
            'g_FormCatSelect.Invoke(New UpdatePropertiesTabDel(AddressOf UpdatePropertiesTab))
            g_FormCatSelect.CmboBox_SelectAType.Items.Add(Entity)
        Next

        ' Make the first category in the list as the default.
        g_FormCatSelect.CmboBox_SelectAType.SelectedItem = g_FormCatSelect.CmboBox_SelectAType.Items.Item(0)
    End Sub
#End Region
End Class

Answer 1

If I understood what you were trying to say correctly I think you could simply use this formula :

=UNIQUE(Table1[Column1]) 

I hope it helps! Please set this as the proper answer if it helped you! :)