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



## TedX (Dec 18, 2022)

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-5R Jones (a)2. CHAIN OF GOLD-4Ms R King3. PREFERRAL (NZ)-7T Berry4. PRINCESS RAYAA-11J Collett5. SILENT RAINDROPS-12S Clipperton6. PINK BAROQUE-10K McEvoy7. IN FRONT-1A Adkins8. OFFANEASY (NZ)-2Ms E Hennessy (a)9. KOVALICA (NZ)-5J B McDonald10. GREEN SHADOWS-1Z T Lloyd (a)11. AWESOME JOHN-7B Avdulla


----------



## Peter_SSs (Dec 18, 2022)

TedX said:


> 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.xlsmA11. CRAFTY EAGLE2-53R Jones (a)42. CHAIN OF GOLD5-46Ms R King73. PREFERRAL (NZ)8-79T Berry104. PRINCESS RAYAA11-1112J Collett135. SILENT RAINDROPS14-1215S Clipperton166. PINK BAROQUE17-1018K McEvoy197. IN FRONT20-121A Adkins228. OFFANEASY (NZ)23-224Ms E Hennessy (a)259. KOVALICA (NZ)26-527J B McDonald2810. GREEN SHADOWS29-130Z T Lloyd (a)3111. AWESOME JOHN32-733B AvdullaSheet1

After

TedX.xlsmA1CRAFTY EAGLE2CHAIN OF GOLD3PREFERRAL (NZ)4PRINCESS RAYAA5SILENT RAINDROPS6PINK BAROQUE7IN FRONT8OFFANEASY (NZ)9KOVALICA (NZ)10GREEN SHADOWS11AWESOME JOHN12Sheet1


----------



## TedX (Dec 18, 2022)

Peter_SSs said:


> Then perhaps try to avoid them when other options are available.
> 
> Assuming that data starts at cell A1, try this (no loops)
> 
> ...



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 (Dec 18, 2022)

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 (Dec 18, 2022)

You're welcome.



TedX said:


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


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 (Dec 19, 2022)

Peter_SSs said:


> 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 (Dec 19, 2022)

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


----------

