VBA: Macro to loop through range and remove certain data after the last hyphen

  • Hoping someone can help me with this -


    I basically have a sheet that includes data like so:


    AAA-IK-10
    BBB-09
    CCC
    DDD-TT-2XL
    EEE-R-L


    And I need a Worksheet_Change macro that runs through column "A" and deletes any data after the last hyphen, if the data corresponds to a list like so:


    -S
    -M
    -09
    -10
    etc.


    Can someone help?


    I found this macro which does a similar job (but not the same) but i can't modify it correctly:



    Thanks in anticipation.
    - jg2703

  • Re: VBA: Macro to loop through range and remove certain data after the last hyphen


    I'm not sure what you are after.


    The code below will only run if the change was made in column A, but the problem is if it gets run a second time, by a value in column A being changed then the values that still have a "-" in them will be changed.


  • Re: VBA: Macro to loop through range and remove certain data after the last hyphen


    See if this is what you are looking to due..



  • Re: VBA: Macro to loop through range and remove certain data after the last hyphen


    Hi jg2703,


    Welcome to the Ozgrid forum.


    I suspect you're looking for something like this, which builds an array of final characters to delete, and compares each set of final characters to the array before removing it.


    However, I would suggest you look at the logic of what you are doing. Do you really want every small change you make to this worksheet, wherever it is and whether or not the rebuild has already taken place, to trigger the macro and rebuild column A? If you don't then you should consider an alternative means of triggering the procedure, one that doesn't involve a worksheet event.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: VBA: Macro to loop through range and remove certain data after the last hyphen



    Batman
    Thanks for your code - works fine, but it comes up with the error 'Unable to get the Match property of the WorksheetFunction class' on the

    Code
    If IsNumeric(WorksheetFunction.Match(EndChars, FindChars, 0)) Then

    line.
    Do you know what's wrong?
    Thanks everyone for the quick + helpful replies!
    jg2703


    -------
    EDIT:


    It seems to be when it finds an ending that isn't in the array.... but there will be loads of them in the list :?:
    is there a way round this?

  • Re: VBA: Macro to loop through range and remove certain data after the last hyphen


    Apologies, I thought that would need an error trap, but obviously didn't test it properly, and didn't put one in. Try this.


    Bear in mind my other comments about the logic of using the Worksheet_Change event. It might be correct, but I don't know the circumstances in which the code wants to be triggered.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!