Hi All!!
I have the below which does exactly what I need to do with a spreadsheet, however I have one addition to make which I can't quite suss.
To start with, the code -
The scenario:
One workbook has 4 tabs/sheets, the fourth tabs column M (sheet name "list of accounts") contains a list of refs that can possibly grow.
We need to allow the above to run, but at the end also filter out anything that is in sheet 4 column M in the current worksheets column F (i.e if column f has a match in m1, m36 (or wherever) this row is then also removed from the current worksheet). After the current ".Range("A1:L1").AutoFilter Field:=6, Criteria1:=arrList, Operator:=xlFilterValues" and before "End With" is where I picture it happening unless someone can suggest better?.
I hope that makes sense?.
Thanks as always!
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11
I have the below which does exactly what I need to do with a spreadsheet, however I have one addition to make which I can't quite suss.
To start with, the code -
Code:
Sub FilteringCP()
Dim RngOne As Range, cell As Range
Dim LastCell As Long
Dim arrList() As String, lngCnt As Long
With Sheets("List of Accounts")
LastCell = .Range("M" & Sheets("List of Accounts").Rows.Count).End(xlUp).Row
Set RngOne = .Range("m2:m" & 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("CP")
If .FilterMode Then .ShowAllData
.Range("A1:L1").AutoFilter Field:=6, Criteria1:=arrList, Operator:=xlFilterValues
End With
End Sub
The scenario:
One workbook has 4 tabs/sheets, the fourth tabs column M (sheet name "list of accounts") contains a list of refs that can possibly grow.
We need to allow the above to run, but at the end also filter out anything that is in sheet 4 column M in the current worksheets column F (i.e if column f has a match in m1, m36 (or wherever) this row is then also removed from the current worksheet). After the current ".Range("A1:L1").AutoFilter Field:=6, Criteria1:=arrList, Operator:=xlFilterValues" and before "End With" is where I picture it happening unless someone can suggest better?.
I hope that makes sense?.
Thanks as always!
A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11