Filter recordset based on worksheet array

Question

I have an array containing values in Sheet1.Range(A1:A10).

After I open my recordset I'd like to filter the data based on values in my array. Is this possible at all?

Thanks

Wouldn't it be easier to restrict the original recordset using a WHERE clause - or do you need the other data too? — Rory
– Rory, Commented Jun 16, 2014 at 10:36
Yes, I've been trying this but not sure how to attach the variant to the end of the WHERE. I need to code something like "WHERE transaction IN [myarray]" — Brian
– Brian, Commented Jun 16, 2014 at 10:39

Rory · Accepted Answer · 2014-06-16 11:13:37Z

1

This is too long for a comment, so here are a couple of functions I use (this assumes strings as the criteria):

Function GetStringInList(rngCriteria As Range) As String
   Dim rngCell           As Range
   Dim strTemp           As String
   For Each rngCell In rngCriteria.Cells
      If Len(rngCell.Value) > 0 Then strTemp = strTemp & ",'" & DoubleQuotes(rngCell.Value) & "'"
   Next rngCell
   ' now strip off leading comma
   GetStringInList = Mid$(strTemp, 2)
End Function
Function DoubleQuotes(strIn As String) As String
   DoubleQuotes = Replace(strIn, "'", "''")
End Function

so when building the SQL you could use something like:

strSQL = strSQL & " WHERE [FieldName] In " & GetStringInList(Range("A1:A10"))

answered Jun 16, 2014 at 11:13

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Over a year ago

Hi Rory, I figured it out in the end. I used a for...next to convert by array to csv then included that in my WHERE. Very useful trick indeed! Thanks

Rory Over a year ago

That's what that function does. :)

Collectives™ on Stack Overflow

Filter recordset based on worksheet array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related