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

**DocAElstein** · 12-26-2018, 10:22 PM

Hello Norman,
This looks fairly easy, but I may have missed something…

But anyway, here we go…
Looking at your sample data, it appears that all we need to do is
_1) to look for a value, or rather a text, of “2018,” at the start each cell .Value
_2) If we do not have the “2018,” then we check to see if we have those “loose numbers”

The simplest thing to do then would be to collect rows of data having the “2018” at the start. One way to do this would be to loop through the rows of data and put the data values having the “2018” at the start into an array that has the size of the complete original data range.
At each loop we will also check to see if we had those “loose numbers” and if so, tack them on to the last output array value. Note: For the code to work, I have assumed that if we have a row of “loose numbers” , then at least the first 4 characters will be part of a number. I also assume that the rows that you do not want will never have something looking like a number as the first 4 digits.

Once we finished that we will have an array that has the wanted rows at the start and the rest of the “rows” in that array will be empty.
So if we then paste that array of values back into the worksheet, then we will get you wanted output

I will give you the code below to do that, and if you then want anything different or want me to explain anything more about what is going on , then let me know.. , I will take another look in, ( maybe tomorrow).
I have put the output in a new worksheet, but to put it in the original worksheet, then I think you can see where to change Tabelle1 to BEFORE or whatever…

Alan
_.______________________________________________

BEFORE
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A
1	Configurable Alerts blabla:
2	Run Date:
3	Run Time:
4	HCR_SYS801
5	16/12/2018
6	22:30:49 UTC
7	Page 1 of 34
8	Enterprise Blabla Mgt
9	78) Blabla allocation by position (medium blabla)
10	This alert gives the monthly blabla of blabla in BLABLA to enable the blabla to fund raising partners.
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,
14	10006098, 15392.64
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,
16	10005678, 16231.7
17	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,
18	14900.28
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,
21	7566.31
22	Configurable Alerts Report ID:
23	Run Date:
24	Run Time:
25	HCR_SYS801
26	16/12/2018
27	22:30:49 UTC
28	Page 2 of 34
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,
32	5325.3
33
34	Configurable Alerts Bla bla:
35	Run Date:
36	Run Time:
37	HCR_SYS801
38	16/12/2018
39	22:30:49 UTC
40	Page 3 of 34
41

Worksheet: BEFORE

After running the code:
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )

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

Worksheet: Tabelle1

Normans data arrays.JPG : https://imgur.com/FchmkiG
Normans data arrays.JPG

Code:

Code:

Sub Just2018lines()
Dim Lr As Long ' variable for last row number with data in it 
 Let Lr = Worksheets("BEFORE").Range("A" & Rows.Count & "").End(xlUp).Row ' Lr : http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192 : https://pastebin.com/1adPpV6J : https://www.excelforum.com/hello-introduce-yourself/1214555-an-old-geezer-coming-over-from-the-access-forum.html
Dim arrIn() As Variant ' We will put our range of data into this. I need Variant, simply because .Value applied to a range returns the data field in variant types
 Let arrIn() = Worksheets("BEFORE").Range("A1:A" & Lr & "").Value
Dim arrOut() As String ' I can define the type as I have a fixed size array that I will be filling in a loop. ( I have to use ReDim Statement below, as Dim only takes numbers)
 ReDim arrOut(1 To UBound(arrIn(), 1), 1 To 1) ' Out output array has the same size as the input array
Dim Cnt As Long, RwOut As Long ' Count of all data rows, Count for "rows" in oputput array
    For Cnt = 1 To Lr Step 1
        If Left(arrIn(Cnt, 1), 5) = "2018," Then ' _1) look for a value, or rather a text, of "2018," at the start each cell .Value
         Let RwOut = RwOut + 1 ' next "row" in output array
         Let arrOut(RwOut, 1) = arrIn(Cnt, 1) '
        Else '
            If IsNumeric(Left(arrIn(Cnt, 1), 4)) Then ' _2) check to see if we have those "loose numbers"
             Let arrOut(RwOut, 1) = arrOut(RwOut, 1) & arrIn(Cnt, 1) ' The last row filled in the output array has the "loose numbers" tacked on
            Else
            End If
        End If
    Next Cnt
 Let Worksheets("Tabelle1").Range("A1:A" & Lr & "").Value = arrOut() 'VBA allows us to paste to the values in range in one go from an array , via the .Value property
 Worksheets("Tabelle1").Columns("A:A").AutoFit
End Sub