[VBA] Replace everything after the rightmost hyphen ( - ) in string within filtered range.

[RockandGrohl]

Hello hello, possibly the last question for the year for me.

Set Rng = Range("D3:D" & Lastrowsd + LastrowCT - 2)
    
    For Each cl In Rng.SpecialCells(xlCellTypeVisible)
        ' replace everything after " - " with "By Air"
    Next cl

The cells in this filtered range can have anything in them:

Classical Andalucia - Heathrow
Andre Rieu - Live in Birmingham - Dublin


Etc etc

What I want to do, is have something loop down the list and replace everything after the rightmost hyphen with "By Air"

So it would look like:

Classical Andalucia - By Air
Andre Rieu - Live in Birmingham - By Air



Everything online appears to be about REMOVING the text, not REPLACING it, even when I google for "Replace" - which is weird.

Thanks.

 

[Fluff]

How about

      Cl.Value = Left(Cl.Value, InStrRev(Cl.Value, "-")) & " By Air"

 

[Peter_SSs]

Edit: Oops, a bit slower typing than Fluff.

I have assumed all the relevant visible cells will have a "-" in them. Try

For Each cl In Rng.SpecialCells(xlCellTypeVisible)
    With cl
      .Value = Left(.Value, InStrRev(.Value, "-")) & " By Air"
    End With
Next cl

 

[RockandGrohl]

Haha, thanks guys. I tested Fluff's and it works. Forgot about InStrRev!

Only thing is, this particular set of data is 600 rows long within a dataset of about 10,000 rows - appears to take a few minutes to replace as it's evaluating each line. Is there possibly a faster way? Cheers.

 

[Fluff]

If the filter criteria is always the same, Evaluate might be faster, but I doubt it would be a significant improvement and could even be slower.

 

[Peter_SSs]

this particular set of data is 600 rows long within a dataset of about 10,000 rows - appears to take a few minutes to replace as it's evaluating each line. Is there possibly a faster way?

Are the 600 rows likely to be in a single contiguous range (or small number of such ranges) or are the visible rows scattered completely throughout the 10,000 row range?