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

Dear all,

I have an excelsheet where the dataset is store in column A in in a CSV format.
I am looking for a code starting from A1 to remove the rows with text string and to move some numbers stored in rows <>"2018" at the end of the previous row. I have posted a question in another thread to adress the text removing part but I am looking for the how to move the numbers <>2018 part.

s an example of the dataset:

Configurable Alerts
Report ID:
Run Date:
Run Time:
TEXT_SYS801
16/12/2018
22:30:49 UTC
Page 1 of 34
Enterprise Blablabla
78) Blablabla data blablabla by blabla (medium priority)
This alert gives bla bla bla bla to partners.
2018,...,...
2018, 1, 95716, 10021703, NA, TEXT (TEXT), PHL, TEXT,Text Text Text,
10031793, 2448.83
2018,...,...
3000.95
2018,...,...
2018,...,...

Configurable Alerts
Report ID:
Run Date:
Run Time:
TEXT_SYS801
16/12/2018
22:30:49 UTC
Page 2 of 34
2018,...,...
2018,...,...
2018,...,...

Any idea?

I have a put a question in the following thread to adjust the first part of the code but have not heard anything yet
http://www.excelfox.com/forum/showth...-on-Conditions

Many thanks for the help and sharing knowledge

Cheers
Norman

Hello again Norman
As I mentioned , I am not too familiar with the code from Admin ( http://www.excelfox.com/forum/showth...tions#post3640 ) , but it sounds like something like your requirement can be done in many ways using VBA.
( Admin is using some sort of advanced filtering technique which I don’t have a lot of experience with.
I would probably use a simple coding technique which might be marginally slower in running, but I doubt that anyone would notice or be worried about the minute increase in time. If you are not too experienced with VBA, then a simpler code might be easier for you to understand and maintain or modify later. )

For me it would be easier if I could see a reduced data example showing me what you have “Before” any code runs, and then show me, perhaps on another worksheet an “After” which shows what you want after the code has run.

The After should be based on the data in the Before. Keep the data to a minimum, but pick it carefully so that it shows all possible scenarios. ( Don’t post any sensitive data, - simply change real data or make the data up, but keep it in a typical form so that it can be used to test the code in all possible scenarios).

At excelfox you can upload a file. If you have not done that before at a forum like this, then it may not be so obvious how to do that. Here are some notes that might help: http://www.excelfox.com/forum/showth...age9#post10769
If you are not familiar with posting at a forum like excelfox , then it might be useful for you to start a practice thread here: http://www.excelfox.com/forum/forumd...p/17-Test-Area . Give the Thread a title anything like “Just practicing posting”. Then you can practice things like uploading a file, using code tags , posting tables .. etc…

I am off for a short time now. I will check back again later
Alan

Hello DocAElstein

Many thanks for the guidance and the info

I have uploaded the file so that you can have a better idea of what is the intended output (After based on Before input)
The cleansing should:
- Delete rows with text strings, dates and blank rows
- Moving values <> 2018 to the preceding row at the end.
I am not sure though if it is easier to use the text to column feature before hand to move the number values at the end of the previous row.

I hope it makes sense and thank you for the help much appreciated

Cheers
Norman

Hi Norman,

Quote:

---

Originally Posted by Norman

...I have uploaded the file so that you can have a better idea of what is the intended output (After based on Before input)

---

I don't see any file ??

No Norman File.JPG : https://imgur.com/PtIxkfN
Attachment 2120

Alan

In the first post you will find the attachment

Quote:

---

Originally Posted by DocAElstein

Hi Norman,

I don't see any file ??

---

Cheers
Norman

OK , sorry , I missed that.
I will take a look at your file when I have time
Alan

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
Attachment 2121

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

---

Dear Alan,

Absolutely amazing!! It works like a charm ...
For someone who is not familiar with VBA, I am really impressed by the code and more importantly by the explanation to understand what is going on behind the scene...really appreciated!!

Many thanks indeed and wish you all the best,

Cheers
Norman

You’re welcome
Thanks for the feedback
Alan

Hello Norman and Doc,

A Filter with wildcard also works:

Code:

---

Sub NormanXLFox(): Dim wa As Worksheet, wb As Worksheet
Set wb = Sheets("Before"): Set wa = Sheets("After")
        wb.UsedRange.AutoFilter 1, "=2018*"
wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy _
            wa.Cells(1, 1)
wb.UsedRange.AutoFilter: End Sub

---

Quote:

---

Originally Posted by xladept

...A Filter with wildcard also works:....

---

well it almost does, it doesnt catch them there rougue numbers does it now...
ThemThereRogueNumbers.JPG : https://imgur.com/hTfWPNE
Attachment 2127


But never the less, a very nice try from a junior member, especially on an evening when he should be too pissed to do anything anyway. ( One day I will learn about these filter things.... )

Nice t c ya
:)
Happy New Year

( But don't give up the day job, just yet.. ;)

Code:

---

Sub NormanXLFox() ' xladept "un coluned"
Dim wa As Worksheet, wb As Worksheet
Set wb = Sheets("BEFORE"): Set wa = Sheets("xladeptAfter")
 wb.UsedRange.AutoFilter 1, "=2018*"
 wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
 wb.UsedRange.AutoFilter ' Will trun filter off
End Sub

---

_____ 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,where's me rogue, numbers then
4	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,where's me rogue, numbers then
5	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,where is me rogue number then
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,where is me rogue number then
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,where is me rogue number then

Worksheet: xladeptAFTERwheresMeRougues

Hi Doc,

I'm not giving up the day job:)

I think this gets the rogues:

Code:

---

Sub NormanXLFox(): Dim wa As Worksheet, wb As Worksheet, r As Long, B
Set wb = Sheets("Before"): Set wa = Sheets("After")
B = wb.UsedRange: wb.UsedRange.AutoFilter 1, "=2018*"
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And _
IsNumeric(Left(Range("A" & r), 1)) Then
Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
End If: Next r
wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy _
            wa.Cells(1, 1)
wa.UsedRange.Replace ",,", ","
wb.UsedRange.AutoFilter: wb.UsedRange = B
End Sub

---

Quote:

---

Originally Posted by xladept

I think this gets the rogues:

---

It certainly does/ ( did the second time around) , you smug g_t , you had to show us how to do it properly didn't you :-)
I'm going to light a firework now, see you next year…
:)

Code:

---


Sub NormanXLFoxAfterAThink() ' 10.03pm
Dim wa As Worksheet, wb As Worksheet, r As Long
Set wb = Sheets("Before"): Set wa = Sheets("AfterAThink")
 wb.UsedRange.AutoFilter 1, "=2018*"
 wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy wa.Cells(1, 1)
    For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And _
        IsNumeric(Left(Range("A" & r), 1)) Then
        Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
        End If
    Next r
wb.UsedRange.AutoFilter
End Sub

Sub NormanXLFoxAfterASecondThink() ' 10.30pm
Dim wa As Worksheet, wb As Worksheet, r As Long
 Set wb = Sheets("Before"): Set wa = Sheets("AfterASecondThink")
 wb.Activate
        wb.UsedRange.AutoFilter 1, "=2018*"
    For r = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
        If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And _
        IsNumeric(Left(Range("A" & r), 1)) Then
        Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
        End If
    Next r
 wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
 wa.UsedRange.Replace ",,", ","
 wb.UsedRange.AutoFilter
End Sub

---

_____ 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

Worksheet: AfterASecondThink

Have a blast Doc old friend:)

On sobering up, this last solution from Mr xladept, looks like a nice solution to look at to get a bit of knowledge about what this “AutoFilter” is about.
So for the benefit of any novices catching this Thread , here is my “take” on what the “AutoFilter” is about, and in particular what we can learn from the last routines..

I haven’t used stuff like “AutoFilter” much myself: It seems to be one of those things that profis, use, but which can be a bit daunting to the novice VBA user..

Here is the last routine in a slightly SAlanitised form.

Code:

---

Sub NormanXLFoxAfterASecondThink_2() ' xladept  http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row?p=10878#post10878
Rem 0
Dim wa As Worksheet, wb As Worksheet: Set wb = Sheets("Before"): Set wa = Sheets("AfterASecondThink")
 wb.Activate ' We are working on wb, which means the the  Range  stuff beow should probably better be  wb.Range  ,  as it they arent, then it might be best to activate that worksheet because usually an unqualifed  Range  call will go to the active worksheet
Rem 1 "The Filter thing" Part 1
        wb.UsedRange.AutoFilter Field:=1, Criteria1:="=2018*"
Rem 2 Loop all rows, "Hidden" and "Visible"
Dim r As Long
    For r = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1 ' For this code we must +1 to be sure to catch any last rouge number, beacus looking at this point, VBA will go back up to the last now "visible" row. This is just how VBA is wired to work. VBA uses and sees both "visible" and "invisible" rows, but  .End(xlUp)  is the VBA equivalent to keys  Ctrl+Up  which takes us to the last  "visible"  cell in a row
        If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And IsNumeric(Left(Range("A" & r), 1)) Then
        Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
        Else
        End If
    Next r
Rem 3 "The Filter thing" Part 2
 wb.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
 wb.UsedRange.AutoFilter
Rem 4
 wa.Activate
 wa.Columns("A:A").AutoFit
End Sub

---

Rem 1 is the main part of the code concerned with “AutoFilter”. ( Typically a code line such as this would form about half of the important code lines related to a use of “AutoFilter” )
The main “AutoFilter” bit in this code line is
______.AutoFilter Field:=1, Criteria:="=2018*"
( There are more optional arguments available, but I don’t understand them all yet ( https://docs.microsoft.com/en-us/off...nge.autofilter ) )
This “AutoFilter” thing is a Method of a Range. In this example it is applied to UsedRange. UsedRange is a bit of a dodgy thing to use, as it often does not give you what you expect. In this example we will get the range A1:A40 if we are lucky.
Field:=__ is column of the range that we are interested in. In our case we only have 1, but if we had a couple of columns then we could use 1 or 2 etc. This determines where VBA is going to look for the criteria you give it in the next argument.
Criteria:=__ is what is looked for. This argument will take “wild cards” , which in this example means basically that VBA will look in the first column of our range for a text which starts with "=2018". The * is taken as meaning anything.

Take look at the code….
( Hit Alt+F11 from the spreadsheet and look for it in a code module,
or
_1 Hit Alt+F8 to get the routine dialogue window )
_2 Select the routine
_3 Select to edit or work on the code
Alt8 SelectRoutine EditCode .JPG : https://imgur.com/aRHLZbC
Attachment 2130

Click anywhere in the routine.
Run it in F8 debug mode and stop close after wb.UsedRange.AutoFilter Field:=1, Criteria:="=2018*" ,
or
Click in the margin close after wb.UsedRange.AutoFilter Field:=1, Criteria:="=2018*" to put a stop in , and run using the play button
Stop Play.JPG : https://imgur.com/9M9uQlU
Attachment 2131

So you want to get at the point where the routine is paused something like this
Paused.JPG : https://imgur.com/alAqyS4
Attachment 2132

At this part of the routine progression, if you look at the main data sheet, you will now see that it has changed from the original , ( Worksheets: BEFORE http://www.excelfox.com/forum/showth...-row#post10870 ) , and now looks lIke
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B
1	Configurable Alerts blabla:
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,
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,
17	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,
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,
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,
41

Worksheet: BEFORE
That screen shot and the next code sections illustrate a lot of what / how the “AutoFilter” is.
When talking in Excel jargon about a spreadsheet looking like that last screenshot we might often say that we have the lines 1, 11, 12, 13 , 15, 17, 19, 20 , 29, 30 , 31 as “visible” in the range A1:A40.
Or we might say the Range A1:A40 has “visible cells” of cells A1, A11, A12, A13 , A15, A17, A19, A20 , A29, A30 , A31. Or we might say something similar, but the word “visible” will likely be used somehow.
( We will explain later why A1 “appears”### )

Rem 2 This section gives a nice insight as to how the “AutoFilter” works.
( The Looping of this section is not so typically seen in coding using “AutoFilter” ).
Because of the particular requirement of this Thread, we need to check if we had any “rouge numbers”, which seem to have slipped off the line they were likely intended to be on and appear in the next line.
So the question is, having used the “AutoFilter” effectively to do the main filtering out of the lines we did not want, how do we now check the lines above our “visible” lines. … Well, the fact that the row height is checked and the complete routine seems to do what we want, illustrates to us that what “AutoFilter” appears to do: It appears to reduce the height of the rows which we do not want to 0 height. As far as we Humans perceive this, we regard such lines as “invisible”. To VBA they are not invisible. To VBA they are lines similar to any others. One of the VBA range properties of what we regard as “invisible” is a row height of 0. In other words our “invisible” row is a “row of zero height” to VBA.
We might say that the routine has at this point “filtered out” lines other than those with the text bit of “2018,” at the start. VBA has actually reduced the row height to 0 of all but the cells with “2018,” at the start.
The looping section of coding here in Rem 2 is similar to the looping in my original code in post #7, except that we no longer need to check for the bit of “2018,”. We simply look at lines of zero height in the now filtered range which also have a row above which has not been “filtered out” . In other words those two criteria are satisfied by
____If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0
If that condition is met, Then the “rogue numbers” are tacked on in a similar way as in my routine.
Here we have
Range("A" & r - 1).Value = Range("A" & r - 1).Value & Range("A" & r).Value
Because this routine differs in general from mine, in that it works directly with the spreadsheet, this step is somewhat more obvious than in my routine. ( My routine uses what is called a VBA arrays type coding, which puts all data in an internal array, manipulates that to produce a final output array which is then pasted into the worksheet in one go. Using “AutoFilter” ) is strictly called the using “Range AutoFilter Method” , and as such comes under the general heading of Worksheet functions which are mostly applied to worksheets. My coding does more traditional elementary coding and mathematics internally to achieve similar final results )

Rem 3
The lines here would be typical of the second part the coding in a simple usage of “AutoFilter”
Usually we would like to have what is to us “visible” after the filtering, to be in some “normal” form.
Doing a simple .Copy of the range of what we “see” will probably give us copied in the clipboard some form of a total range of discontinuous cells, in our case something like A1,A11:A13,A15,A17,A19:A20,A29:A31. VBA tends in such a case of all the cells being “in line” to actual Hold that as if it was single 11 row range. So copying and pasting will likely get us near what we finally want.
There tends in the practice to be odd things that might catch you out doing that simple copy and paste. One thing to note is that the “AutoFilter” often catches the first row whether it meats the criteria or not###. ( This is generally regarded as an option to keep the header row in any filtering work, which is often useful practically ). Sometimes adding a bit in a code line to be sure of copying just our “visible” rows is often a good idea. Taking these things into account, and knowing what you are doing, ( which I don’t too well here ) , will mean that rather than a simple line like
wb.UsedRange.Copy Destination:=wa.Cells(1, 1)
Instead something like this would be used to be sure to get what you want
wb.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
The .Offset(1, 0) is perhaps easy to understand as this will mean that we will not get the header row copied.
The .SpecialCells(xlCellTypeVisible) in one aspect is easy to understand: it will limit us to not going too far down the worksheet with our copying. Copying to our last row would probably often do the same, but there are likely subtle reasons only to be gained by experience to favour the use of .SpecialCells(xlCellTypeVisible).
The things talked about, in particular in this Rem 3 code section, are the subtleties that can trip up the inexperienced. That is why “AutoFilter” way tends to be the way a profi might do it. So it is probably better left to them to do it. I tend to avoid it for fear of getting it wrong.
( Mr xladept of course, is, in reality, not quite what you might call a “Junior” thing, so he probably knows what he is doing …. :-) )

Nice analysis Doc,

Here's a little better version:

Code:

---

Sub NormanXLFox(): Dim wa As Worksheet, wb As Worksheet, r As Long, B
Set wb = Sheets("Before"): Set wa = Sheets("After")
B = wb.UsedRange: wb.UsedRange.AutoFilter 1, "=2018*"
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And _
IsNumeric(Left(Range("A" & r), 1)) Then
Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
End If: Next r
wb.UsedRange.Replace ",,", ","
wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy _
            wa.Cells(1, 1)
wb.UsedRange.AutoFilter: wb.UsedRange = B: End Sub

---

Quote:

---

Originally Posted by xladept

.a little better version:..

---

Thanks, I see you store the original range now, ( B ), then put it back when ya finished. … that is certainly helpful in developing and working on the analysis as it was a pain when that changed and I wanted to start the routine again. .
I expect I may come back Later, and tack a bit on to the analysis here. Maybe I will do that when I stumble on some old posts of mine on “AutoFilter” . Maybe much later.. I don’t know. The Winter is disappointing again with not much snow. So I may go back outside building things ‘till next year and just do a little Excelling in the morning.
( A useful thing about excelfox is the longer Edit time. So I come back and improve stuff when I feel in the mood).

Take care
_.________________________________________________ ________________________________________________

Further analysis here … later maybe….

( BTW , I forgot to add at the end of my last analysis that a final line like …_
____.AutoFilter
_... is often seen in routines like this.
Using .AutoFilter without any arguments seems to “switch the thing off”, something like a toggle switch putting it back in the “other” state where all the rows are “visible” as they were before. .. But I will come back maybe later and tidy my analysis up a bit…
:-)

Hi Doc,

Again your analysis is right-on:)

I never see snow anymore and I used to teach cross-country skiiing:(

If I am not mistaken, this non-looping macro should also work...

Code:

---

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

---

@ 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?

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… :)

---

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]

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… :)

---

Quote:

---

Originally Posted by 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?

---

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...

Code:

---

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)...

Code:

---

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

---

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

Quote:

---

Originally Posted by Rick Rothstein

.... 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

Code:

---

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