Hello everyone,
I have been trying a code that does not seem to work. I have a range in column F on the first sheet that is dynamic. Today it can be F2:F4 but tomorrow it can be F2:F10 (F2 is always that first cell in the range).
What I am trying to do is filter on the second sheet all cells in Column A that "begins with" my dynamic range.
As an Example I am trying the following:
Sheet1 (Range F2:F4)
1
2
3
4
I am trying to add a filter in the second sheet so Excel only shows the cells that contain the string that begins with the above values (cells on sheet two that start with the values 1,2,3 and 4)
So far I have the following code:
The issue is that the filtered area is blank (no values are returned).
Any ideas?
Your help is greatly appreciated.
I have been trying a code that does not seem to work. I have a range in column F on the first sheet that is dynamic. Today it can be F2:F4 but tomorrow it can be F2:F10 (F2 is always that first cell in the range).
What I am trying to do is filter on the second sheet all cells in Column A that "begins with" my dynamic range.
As an Example I am trying the following:
Sheet1 (Range F2:F4)
1
2
3
4
I am trying to add a filter in the second sheet so Excel only shows the cells that contain the string that begins with the above values (cells on sheet two that start with the values 1,2,3 and 4)
So far I have the following code:
Code:
Sub Filter1()
Dim RngOne As Range, cell As Range
Dim LastCell As Long
Dim arrList() As String, lngCnt As Long
With Sheets("Sheet1")
LastCell = .Range("F" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
Set RngOne = .Range("F2:F" & LastCell)
End With
'load values into an array
lngCnt = 0
For Each cell In RngOne
ReDim Preserve arrList(lngCnt)
arrList(lngCnt) = cell.Text & "*"
lngCnt = lngCnt + 1
Next
With Sheets("Sheet2")
If .FilterMode Then .ShowAllData
.Range("A:A").AutoFilter Field:=1, Criteria1:=arrList, Operator:=xlFilterValues
End With
End Sub
The issue is that the filtered area is blank (no values are returned).
Any ideas?

Your help is greatly appreciated.