Results 1 to 2 of 2

Thread: VBA Code to Remove Larger Values between Two Delimiters

  1. #1

    Question VBA Code to Remove Larger Values between Two Delimiters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    Try this...

    Code:
    Sub KeepFirst()
    Dim rMyRng As Range, r As Range, sSep As String, iPos As Integer
    
    sSep = "|"
    Set rMyRng = Application.InputBox("Select the Range", "Range Input Req.", , , , , , 8)
    
    For Each r In rMyRng
        iPos = InStr(1, r.Value, sSep)
        If iPos Then r.Value = Left(r.Value, iPos - 1)
    Next r
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •