Just testing a before a possible Thread post. No reply needed

[DocAElstein]

.... this non-looping macro should also work...

I sometimes wonder whether we fool ourselves into thinking that these things are non looping. I don’t think any of us is privy to exactly what Excel is doing behind the scenes when these things work. The lack of clear documentation to these “array” type workings and the little fiddles you have to do sometimes to get these to work, makes me wonder if anyone knows exactly what is going wrong. Here is one of not many theories about how these sort of things might work.. http://www.excelfox.com/forum/showth...on-and-VLookUp That might suggest that what happens with these Evaluate Range things is similar to how maybe the CSE Array Entry formulas work in spreadsheets:
It suggests that we maybe “tap in” to something that gets done anyway whether we want to or not, and as such we don’t add too much extra. This something could be a “along the row, down a column, along a row…” type thing like a raster across an old telly screen which updates a complete screen/ worksheet. When we define the area in the initial selection of the spreadsheet area before adding our formula in a type 2 CSE Array Entry we may simply be exposing a large area rather than the usual single cell in a complete screen update. In other words the extra information for a multi cell range is there anyway and all we do is open up a bigger window so that we see it at once.
As far as I know, Evaluate was / is something to let you construct a string using both Excel spreadsheet and VBA stuff and then evaluate that string as if you wrote it manually in a spreadsheet cell.
Whether or not it was planned that it could be used in the way that people like Rick do as a sort of hidden type 2 CSE Array Entry formula , or whether that is an accident, I don’t know.

I don’t think I have ever noticed that these things wont work as a type 2 CSE Array Entry , ( occasionally they don’t work in evaluate and we have to coerce them into working ) , so taking Ricks last masterpiece and putting it in a spreadsheet like this will confirm that it works

Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 Range("B1:B" & LastRow).FormulaArray = "=" & strEval
 Debug.Print strEval 'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A1," ",""),",","")),IF(LEFT(A1:A0,4)="2018",TRIM(A1:A0&" "&A2:A1),""),IF(LEFT(A1:A0,4)="2018",A1:A0,""))

_____ Workbook: NormanOrrinFilter.xlsm ( Using Excel 2007 32 bit )

Row\Col	B
1
2
3
4
5
6
7
8
9
10
11	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
12	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
14
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla, 10005678, 16231.7
16
17	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234 14900.28
18
19	2018, 1, 90919, 10020984, P2, Ass BLA Balbla, CHE, BLA,Blabla, 10033098, 10486.33
20	2018, 1, 95706, 10023098, NB, Assc BLA Blabal (LatBLAa), BLA, BLABLABLA,Blabla, 10034318, 7566.31
21
22
23
24
25
26
27
28
29	2018, 1, 95716, 10018763, NA, Asst BLA Off (Blabla & Multi-BLa), BLA, BLA,Bla, 10097776, 8607.96
30	2018, 1, 99716, 10026132, G5, Snr BLA Asst (Bla Blabla), BLA, BLABLA,bla BLa, 18767043, 5477.44
31	2018, 1, 99716, 10016545, G6, Blabla Blabla (BLA), BLA, BLABLABLA,Blabla, 1097029, 5325.3
32
33

Worksheet: Rick
_.________________________________--

I would guess that whoever wrote the .SpecialCells did that quite efficiently. Who knows, they may even have used some of this “hidden array “ techniques to do it.
So a combination of .SpecialCells and evaluate range one liners sounds an attractive combination , in my opinion.

_._________
Coming back to the solution from Rick… I was interested to work through it.. so I did. I put my workings here, as the OP or anyone else viewing this thread might be interested.

Alan