I wonder if you'd be better off looking at this task in a different way. At the moment you appear to be talking in terms of a database of employee details that you'd like to run a query on and then output the results to Excel. Obviously this would better suit a genuine database application but you could mimic that task in Excel using a worksheet as the data storage, a UserForm as the query interface and VBA to manage the data processing. Your question seems to want simply to automate some Excel keystrokes in an efficient manner, whereas it might be better to ask how VBA could create this mini database application.
In particular, for genuine efficiency, you really need to steer clear of multiple interrogations of Worksheet values, such as with VLOOKUP, FIND, MATCH etc. as reading values is time-consuming. You're usually better off reading the Worksheet database values just once into an array and then interrogating that array. Fortunately, these tasks are really quite straight forward in VBA.
So ... first of all, I'd create a UserForm with ListBoxes for each filter that you want to use. In the example below, I've just done three but you could do all of the columns if you wished. Make sure you set the MultiSelect property to 1-fmMultiSelectMulti:
In the code behind the UserForm, add the following (you can change the name of the listboxes if you wish, but I've used lboxCountry, lboxState and lboxDept):
Public Property Get Lbox(index As Long) As MSForms.ListBox
Select Case index
Case COUNTRY_ID: Set Lbox = lboxCountry
Case STATE_ID: Set Lbox = lboxState
Case DEPT_ID: Set Lbox = lboxDept
End Select
End Property
Private Sub btnProcess_Click()
Call OutputFilteredResults
End Sub
Now just add the data reading and data filtering procedures into a Module, like so:
Option Explicit
Public Const COUNTRY_ID As Integer = 1
Public Const STATE_ID As Integer = 2
Public Const DEPT_ID As Integer = 3
Public Const EMP_NUM_ID As Integer = 4
Public Const EMP_NAME_ID As Integer = 5
Public Const SALARY_ID As Integer = 6
Private mDb As Variant
Public Sub ReadDatabase()
Dim lastRow As Long
Dim r As Long
Dim c As Long
Dim uniques(1 To 6) As Collection
Dim v As Variant
'Read database from worksheet
With ThisWorkbook.Worksheets("Sheet1") 'ammend as req'd
lastRow = .Cells.Find(What:="*", _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False).Row
mDb = .Range(.Cells(2, COUNTRY_ID), .Cells(lastRow, SALARY_ID)).Value2
End With
'Find unique values for filtering
For r = 1 To UBound(mDb, 1)
For c = 1 To 6
If r = 1 Then Set uniques(c) = New Collection
v = Empty: On Error Resume Next
v = uniques(c).item(CStr(mDb(r, c))): On Error GoTo 0
If IsEmpty(v) Then
uniques(c).Add CStr(mDb(r, c)), CStr(mDb(r, c))
End If
Next
Next
'Populate the filter boxes
For c = 1 To 3
For Each v In uniques(c)
UserForm1.Lbox(c).AddItem v
Next
Next
UserForm1.Show False
End Sub
Public Sub OutputFilteredResults()
Dim r As Long
Dim c As Long
Dim v As Variant
Dim hits As Collection
Dim output() As Variant
'Acquire the target rows for output
Set hits = New Collection
For r = 1 To UBound(mDb, 1)
If IsAMatch(r) Then hits.Add r
Next
'Transfer data to output array
ReDim output(1 To hits.Count, 1 To 6)
r = 1
For Each v In hits
For c = 1 To 6
output(r, c) = mDb(v, c)
Next
r = r + 1
Next
'Write the output
With ThisWorkbook.Worksheets("Sheet2") 'ammend as req'd
.Cells.Clear
.Cells(1, 1).Resize(, 6).Value = _
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Resize(, 6).Value2
.Cells(2, 1).Resize(UBound(output, 1), UBound(output, 2)).Value = output
.Activate
End With
End Sub
Private Function IsAMatch(r As Long) As Boolean
Dim i As Long
Dim c As Long
Dim found As Boolean
'Loop through the Listboxes and test for a match in each
For c = 1 To 3
found = False
With UserForm1.Lbox(c)
For i = 0 To .ListCount - 1
found = (.Selected(i) And .List(i) = CStr(mDb(r, c)))
If found Then Exit For
Next
End With
If Not found Then
IsAMatch = False
Exit Function
End If
Next
IsAMatch = True
End Function
You could go further, such as by utilising LINQ syntax to simplify the query code, but this example should give you an idea of how you could think about VBA as more of an application solution.
