VBA Code to Remove Larger Values between Two Delimiters

E

eonblue2

New member
Joined
Feb 27, 2014
Messages
1
Reaction score
0
Points
0
I have a set of data in column A that looks like this:


White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|


The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.


The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.


Tried various methods but nothing seems to work. Any help is appreciated.


Thanks,
-EB
 
Try this...

Code: 
[COLOR=darkblue]Sub[/COLOR] KeepFirst()
[COLOR=darkblue]Dim[/COLOR] rMyRng [COLOR=darkblue]As[/COLOR] Range, r [COLOR=darkblue]As[/COLOR] Range, sSep [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], iPos [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]

sSep = "|"
[COLOR=darkblue]Set[/COLOR] rMyRng = Application.InputBox("Select the Range", "Range Input Req.", , , , , , 8)

[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] r [COLOR=darkblue]In[/COLOR] rMyRng
    iPos = InStr(1, r.Value, sSep)
    [COLOR=darkblue]If[/COLOR] iPos [COLOR=darkblue]Then[/COLOR] r.Value = Left(r.Value, iPos - 1)
[COLOR=darkblue]Next[/COLOR] r
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
You must log in or register to reply here.
Back
Top