Referencing an array of row numbers in a Filter formula.

[HighAndWilder]

This code, just put together to demonstrate the issue, works fine but I want to reference the array of row numbers in the
fomula to just select those rows from the list of towns.

Please advise if there is an altogether better way.

Thanks.

Public Sub subFilterByRow()
Dim arrRows(1 To 5) As Integer
Dim Q As String
Dim arrData() As Variant
Dim strFormula As String

  ActiveWorkbook.Save

  Q = Chr(34)
  
  arrRows(1) = 2
  arrRows(2) = 3
  arrRows(3) = 4
  arrRows(4) = 5
  arrRows(5) = 6
    
  ' I currently write the above array to a sheet and reference the range in the formula
  ' but I want to reference the array directly in the formula.
  Worksheets("Temp").Range("A1:A5").Value = Application.Transpose(arrRows)

  strFormula = "INDEX(LET(d,HSTACK(ROW(tblTowns),tblTowns[Town]),n,Temp!A1:A" & UBound(arrRows) & _
    ",FILTER(d,ISNUMBER(MATCH(INDEX(d,,1),n,0))," & Q & Q & ")),,2)"
  
  ' Populate an array with the results of the formula.
  arrData = Evaluate(strFormula)
  
End Sub

Sample Data.xlsm
	A
1	2
2	3
3	4
4	5
5	6
6
Temp

Sample Data.xlsm
	A	B	C
1		Town
2		Abingdon-on-Thames
3		Accrington
4		Acle
5		Acton
6		Adlington
7		Alcester
8		Aldeburgh
9		Aldershot
10		Alford
11		Alfreton
12		Alnwick
13		Alsager
14		Alston
15		Alton
16		Altrincham
17		Amble
18		Ambleside
19		Amersham
20		Amesbury
21		Ampthill
22		Andover
23		Appleby-in-Westmorland
24
Towns

 

[JEC]

To show you

Sub jec()
 Dim arrData, ar, arrRows(1 To 5) As Long
 ar = Sheet1.ListObjects("tblTowns").DataBodyRange
 
 arrRows(1) = 2
 arrRows(2) = 3
 arrRows(3) = 4
 arrRows(4) = 5
 arrRows(5) = 6
 
 arrData = Application.Index(ar, arrRows, 0)
 
End Sub

 

[HighAndWilder]

To show you

Sub jec()
 Dim arrData, ar, arrRows(1 To 5) As Long
 ar = Sheet1.ListObjects("tblTowns").DataBodyRange
 
 arrRows(1) = 2
 arrRows(2) = 3
 arrRows(3) = 4
 arrRows(4) = 5
 arrRows(5) = 6
 
 arrData = Application.Index(ar, arrRows, 0)
 
End Sub

Ah Ha, that's the way to do it.

My life will never be the same again now.

Thanks JEC.

 

[JEC]

Great! haha cheers

 

[Peter_SSs]

Ah Ha, that's the way to do it.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.