Creating a parameterized query in Excel with SQL using a cell as a parameter

Question

So in MS Excel I've imported a table from a database in a SQL Server. I want to create a parameterized query where you have two cells. Say these two cells are G1 and G2. G1 takes a parameter/category and G2 takes a value from the parameter/category and queries the table you imported (essentially a WHERE clause that is dynamic from cell input). Can someone show me how to do this?

Answer 1

EDIT: Based on a chat session, we discovered that the first parameter is the column to be searched and the second parameter is the value to filter.

---

You can do what you want by filtering the table you imported.

Use the code below as your template. Modify it to reference the correct worksheets and ranges.

Sub FilterByParameter()
    Dim wb As Workbook
    Dim dataSheet As Worksheet
    Dim parameterSheet As Worksheet
    Dim rng As Range
    Dim filterColumn As Long
    Dim filterValue As String

    Set wb = ThisWorkbook
    ' sheet that contains your table
    Set dataSheet = wb.Sheets("Sheet1")
    ' sheet that contains your parameters
    Set parameterSheet = wb.Sheets("Sheet2")
    ' range that contains your table, hard-coded here
    ' but can easily be set dynamically
    Set rng = dataSheet.Range("A1:F78")

    ' get the column you are searching
    filterColumn = parameterSheet.Range("G1").Value

    ' get the value you want to filter on
    filterValue = parameterSheet.Range("G2").Value

    ' turn off autofilters if set
    dataSheet.AutoFilterMode = False

    ' autofilter using your column and filter
    rng.AutoFilter field:=filterColumn, Criteria1:=filterValue

    ' now you can do whatever you want to with the rows
    ' that remain after the autofilter was applied
End Sub

See Efficient way to delete entire row if... for an example of how to use the visible rows.