Move values in rows at the end of the preceding row *SOLVED*

[xladept]

@ Rick - Yes it works

I was unable to use the line continuation (_) to make the code fit within the page of a book - Is it possible?

[DocAElstein]

I say hooray for the horizontal scroll bar !!  …long may it be and long may it be …. :-)   . That’s a lovely one liner.  I’ll be back some time to 'comment on it once I have done a bit of analysis on it… :)

Edit:
and auch Rick's Table Scroll bar to coerce it to show in Internet Explorer also
[CODE][table="width: 500"]
[tr]
[td][/td]
[/tr]
[/table]
[/CODE]

I say hooray for the horizontal scroll bar !!  …long may it be and long may it be …. :-)   . That’s a lovely one liner.  I’ll be back some time to 'comment on it once I have done a bit of analysis on it… :)

[Rick Rothstein]

@ Rick - Yes it works

I was unable to use the line continuation (_) to make the code fit within the page of a book - Is it possible?

The argument to the Evaluate function (which is a large percent of the code line) is a text string, so you cannot just insert line continuations within that portion of the code line... rather, you have to break the text into smaller pieces and insert quote marks on the "broken" ends of the text that you create (making each part a whole complete text string and then concatenate them together using an ampersand, then you can insert the line continuation outside of the individual text strings. Here is a small sample of what I am talking about...

Text = "One two three four five"

becomes...

Text = "One two three " & "four five"

now insert a line continuation...

Text = "One two three " & _
       "four five"

Okay, with that said, here is how I did it for my code (you can, of course, change it if you want longer lines)...

Sub ThisShouldWork()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A1:A" & LastRow) = Evaluate(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("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

[DocAElstein]

Hi
Just some passing comments out of general interest..
_1 ) Evaluate range stuff

.... 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 on. 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. Hence it can be a very efficient way of doing things. This something could be a “along the columns, down a row, along the columns…” 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.
Or what actually may happen is that some Controlled Shifting is done of the current single position in the screen updating done after Enter, so that each individual calculation and displayed result is/as consecutively done is displayed at the appropriately shifted position in the spreadsheet.
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.
Because Evaluate “does” spreadsheet stuff, but is not tied to a spreadsheet, it doesn’t have the restriction of a simple formula calculation of being “tied” to a single cell. So all that gets done and all that is then available after an “update” is there available in a field of values whose offsets or relative positions are exactly in line with the relative positions of the ranges or arrays used in the complete formula that Evaluate evaluates. The field or array of values is floating in space and can be tied down, ( pasted out via the .Value property) to a range, as can any array of values.
Whether or not it was planned that it could be use 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 offering and putting it in a spreadsheet like this is an alternative way to 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

If you run that above code on the OPs test data then you get the correct results, ( the results before the .SpecialCells code line )
_____ Workbook: NormanOrrinRickFilter.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
_.________________________________--

_ 2) SpecialCells
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, http://www.excelfox.com/forum/showth...0899#post10899 as the OP or anyone else viewing this thread might be interested.

Alan