Dynamic range advanced filtering

n0ctis

New Member
Joined
Sep 8, 2014
Messages
3
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:


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? :confused:

Your help is greatly appreciated.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You're using an autofilter, not an advanced filter, and you can't use an array of values as a 'contains' or 'begins with' filter in an autofilter.
 
Upvote 0
You're using an autofilter, not an advanced filter, and you can't use an array of values as a 'contains' or 'begins with' filter in an autofilter.

Hello RoryA,

Thank you for your reply.
I tried changing from AutoFilter to AdvancedFilter but I still can`t get it to work.

A bit of example code would be greatly appreciated as I am a beginner in VBA.
 
Upvote 0
Something like this assuming you have the correct header in F1 on Sheet1:

Code:
Sub Filter1()

Dim RngOne As Range

With Sheets("Sheet1")
       Set RngOne = .Range("F1:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)
End With


With Sheets("Sheet2")

    If .FilterMode Then .ShowAllData

    .Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Criteriarange:=RngOne

End With

End Sub
 
Upvote 0
Glad to help. Welcome to the forum too! :)
 
Upvote 0

Forum statistics

Threads
1,226,617
Messages
6,192,043
Members
453,691
Latest member
CT30

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top