Looping through the Array, counting the matches to avoid resizing the return Array is very efficient.
Application.Transpose
is not needed because the return array is in the same shape as the original array.
RemoveRows2D = Application.Transpose(arr_new)
This is correct:
RemoveRows2D = arr_new
Using Application.Index
to slice off the target column is a cool trick but is it really needed? Not only does it add extra overhead but it adds an extra variable. In my opinion, arr_col_k
is just extra clutter because I don't think that arr_col_k(i, 1) = strKeeper
is any more readable than arr(i, x) = strKeeper
.
arr_col_k = Application.Index(arr, 0, k)
You can remove up_new = up_new + 1
from this loop:
If Headers Then 'handle header row
For j = LBound(arr, 2) To UBound(arr, 2)
arr_new(i, j) = arr(i, j)
Next
i = 1 + 1
up_new = up_new + 1
End If
Moving forward, the main thing that I think you should focus on is the names of your variables. Consider arr, k, strKeeper, up_new, arr_col_k, arr_new
, what do the names of these variable tell you about the roles that they play?
Compare them to my alternative names.
- arr: Data, DataArray, arrData, arrSource, Source, SourceArray
- k: col, ColumnIndex
- strKeeper: Match
- I use Match because it is used in many VBA and Excel functions.
- up_new: Count
- arr_col_k: Matches, arrTarget, ???I would have to think about this one.
- arr_new: Results, results, arrResults
I also use 1 or 2 character variable names for all my counters. I prefer x, x1, y, y1
because i
or l
can be tough to differentiate.
RemoveRows2D
is basically a 2D version of the built in VBA.Filter
function. Whenever I write a version of a standardized method I start by copying the methods signature from the Object Browser and work from there. Using the same structure and variable names as the built-in methods will make it easier for others to understand and use yours.
Question: Is there a more efficient way to nest the following:
If (arr_col_k(i, 1) = strKeeper And re_Tain) Or (arr_col_k(i, 1) <> strKeeper And Not re_Tain) Then
Technically, using If
and EsleIf
blocks is more efficient because the VBA evaluates all the conditions in an If Then
statement. In your case the difference in efficiency is negligible. Note: In my version of function I use a flag with If
and EsleIf
blocks because I think that it read better and is easier to debug.
If (arr_col_k(i, 1) = strKeeper And re_Tain) Then
ElseIf (arr_col_k(i, 1) <> strKeeper And Not re_Tain) Then
End If

Function Filter2D(SourceArray, Match As String, ColumnIndex As Long, Headers As Boolean, Optional Include As Boolean = True, Optional Compare As VbCompareMethod = vbBinaryCompare) As Variant()
Dim count As Long, x As Long, y As Long
Dim flag As Boolean
Dim Results() As Variant
'Adjust the ColumnIndex based on SourceArray Lbound
'This allows the user to refer to the 1st column of a Option Base 0 array as 1 instead of 0
ColumnIndex = ColumnIndex - LBound(SourceArray, 2) + 1
'First loop to count matches
For x = LBound(SourceArray) To UBound(SourceArray)
If x = LBound(SourceArray) And Headers Then
count = count + 1
ElseIf Include Then
If StrComp(SourceArray(x, ColumnIndex), Match, Compare) = 0 Then count = count + 1
Else
If StrComp(SourceArray(x, ColumnIndex), Match, Compare) <> 0 Then count = count + 1
End If
Next
If count = 0 Then Exit Function
'Adjust the count based on SourceArray Lbound
count = count - LBound(SourceArray) + 1
'Size the Results array to fit the data
ReDim Results(LBound(SourceArray) To count, LBound(SourceArray, 2) To UBound(SourceArray, 2))
'Reset count for reuse
count = LBound(SourceArray)
'Second loop to transfer the data
For x = LBound(SourceArray) To UBound(SourceArray)
If x = LBound(SourceArray) And Headers Then
flag = True
ElseIf Include Then
If StrComp(SourceArray(x, ColumnIndex), Match, Compare) = 0 Then flag = True
Else
If StrComp(SourceArray(x, ColumnIndex), Match, Compare) <> 0 Then flag = True
End If
If flag Then
For y = LBound(SourceArray, 2) To UBound(SourceArray, 2)
Results(count, y) = SourceArray(x, y)
Next
count = count + 1
End If
flag = False
Next
Filter2D = Results
End Function