Loops aren't easy if you're scared of them

[TedX]

Hi Friends,

I get some data for a horse race which includes a number, the horse's name, then another number then a person's name. The pattern repeats over and over and all I want is the horse's name. So if you look at the first row, I don't want the number, the dot or the space, what I do want is CRAFTY EAGLE without any trailing spaces. The pattern is, I want to keep row 1, then 4, then 7 and so on. In other words, I want to delete the following two rows after every row I want to keep. I've coloured in the rows I don't want in pink. I thought if the loop doing the row deletions was running, it might as well trim up the name by getting rid of the number, dot and space. My issue is, there will be one-digit and two-digit numbers, for example: (1. Crafty Eagle and 11. Awesome John). As a side note, so you feel sorry for me, after numerous attempts, and watching dozens of videos and tutorials on Loops, there is something about them that messes with my head, I think I am just scared of them, it's psychological. Can someone please write a macro that creates the loop, there are likely to be around 550 horse names on a busy day. TIA

1. CRAFTY EAGLE
-5
R Jones (a)
2. CHAIN OF GOLD
-4
Ms R King
3. PREFERRAL (NZ)
-7
T Berry
4. PRINCESS RAYAA
-11
J Collett
5. SILENT RAINDROPS
-12
S Clipperton
6. PINK BAROQUE
-10
K McEvoy
7. IN FRONT
-1
A Adkins
8. OFFANEASY (NZ)
-2
Ms E Hennessy (a)
9. KOVALICA (NZ)
-5
J B McDonald
10. GREEN SHADOWS
-1
Z T Lloyd (a)
11. AWESOME JOHN
-7
B Avdulla

 

[Peter_SSs]

Loops, there is something about them that messes with my head

Then perhaps try to avoid them when other options are available.

Assuming that data starts at cell A1, try this (no loops)

Sub DelRws()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(left(#,1)+0),replace(#,1,find(""."",#)+1,""""),true)", "#", .Address))
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
  End With
End Sub

Before

TedX.xlsm
	A
1	1. CRAFTY EAGLE
2	-5
3	R Jones (a)
4	2. CHAIN OF GOLD
5	-4
6	Ms R King
7	3. PREFERRAL (NZ)
8	-7
9	T Berry
10	4. PRINCESS RAYAA
11	-11
12	J Collett
13	5. SILENT RAINDROPS
14	-12
15	S Clipperton
16	6. PINK BAROQUE
17	-10
18	K McEvoy
19	7. IN FRONT
20	-1
21	A Adkins
22	8. OFFANEASY (NZ)
23	-2
24	Ms E Hennessy (a)
25	9. KOVALICA (NZ)
26	-5
27	J B McDonald
28	10. GREEN SHADOWS
29	-1
30	Z T Lloyd (a)
31	11. AWESOME JOHN
32	-7
33	B Avdulla
Sheet1

After

TedX.xlsm
	A
1	CRAFTY EAGLE
2	CHAIN OF GOLD
3	PREFERRAL (NZ)
4	PRINCESS RAYAA
5	SILENT RAINDROPS
6	PINK BAROQUE
7	IN FRONT
8	OFFANEASY (NZ)
9	KOVALICA (NZ)
10	GREEN SHADOWS
11	AWESOME JOHN
12
Sheet1

 

[TedX]

Then perhaps try to avoid them when other options are available.

Assuming that data starts at cell A1, try this (no loops)

Sub DelRws()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(isnumber(left(#,1)+0),replace(#,1,find(""."",#)+1,""""),true)", "#", .Address))
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
  End With
End Sub

Before

TedX.xlsm
	A
1	1. CRAFTY EAGLE
2	-5
3	R Jones (a)
4	2. CHAIN OF GOLD
5	-4
6	Ms R King
7	3. PREFERRAL (NZ)
8	-7
9	T Berry
10	4. PRINCESS RAYAA
11	-11
12	J Collett
13	5. SILENT RAINDROPS
14	-12
15	S Clipperton
16	6. PINK BAROQUE
17	-10
18	K McEvoy
19	7. IN FRONT
20	-1
21	A Adkins
22	8. OFFANEASY (NZ)
23	-2
24	Ms E Hennessy (a)
25	9. KOVALICA (NZ)
26	-5
27	J B McDonald
28	10. GREEN SHADOWS
29	-1
30	Z T Lloyd (a)
31	11. AWESOME JOHN
32	-7
33	B Avdulla
Sheet1

After

TedX.xlsm
	A
1	CRAFTY EAGLE
2	CHAIN OF GOLD
3	PREFERRAL (NZ)
4	PRINCESS RAYAA
5	SILENT RAINDROPS
6	PINK BAROQUE
7	IN FRONT
8	OFFANEASY (NZ)
9	KOVALICA (NZ)
10	GREEN SHADOWS
11	AWESOME JOHN
12
Sheet1

Too easy, Peter_SSs you have got to stop saving me, how is it that you know I'm just never going to get VBA to a deep level, is it true that some people just aren't wired for it? Okay, so back to your solution, thank you, yes it worked perfectly for me as it did for you. Whilst I have you, can you just answer a really basic question, just answer it in English, no code is required. On the same worksheet, I'm going to have times which they express like this: 00:33.6 which is 33 seconds and 6 tenths I suppose. It's certainly 33 seconds+ when I click on the cell it states 12:00:34 AM which is another thing that screws with my brain, like Time Zones, Gravity and Taxes. Anyway, how can I convert it to just 33.6 seconds (33 point 6 seconds), life would be easier if everyone just said and wrote 33.6 seconds

 

[TedX]

Actually, forget the part about the time, I sorted that with this stunning bit of kit

Selection.Select
    Selection.NumberFormat = "ss.0"

So once again, thank you for the non-loop fix, I've tried reading those glorious lines of code you wrote and I swear, I think I could learn hieroglyphics easier than understanding what the heck, it actually means ~~~ I guess that's why they call it code

 

[Peter_SSs]

You're welcome.

Actually, forget the part about the time, I sorted that with this stunning bit of kit

Selection.Select
    Selection.NumberFormat = "ss.0"

BTW, in this code you can scrap the Selection.Select line as that does nothing at all. In addition, it is rarely required to select anything to work with it and selecting generally slows your code. So most likely there is a more efficient way than Selection.NumberFormat = "ss.0" but without knowing exact details of what you have, where it is etc., it is hard to advise further. Anyway, if it is working how you want, really no need to change anything.

 

[TedX]

BTW, in this code you can scrap the Selection.Select line as that does nothing at all.

I am so glad you told me that, I have been using it for a long time, as a sort of 'starting point' because I saw it somewhere early on when first looking at VBA. It's amazing how you fall into a bad habit and just keep doing it until someone pulls you up and tells you, there is no need, like you have. There should be more people like you Peter_SSs - thank you again, for me personally, but seeing I'm a little left of centre, I want to thank you on behalf of everyone you and your colleagues have helped, in your case, an amazing 57,647 messages since 2005. This is a simply stunning record. There really is only one way for any of us to repay you and that is, by us writing clean, more elegant code. Merry Christmas

 

[Peter_SSs]

Cheers, thanks for your kind words. Merry Christmas to you too.