Re: VBA: Macro to loop through range and remove certain data after the last hyphen
Quote from Batman;768930Display More
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.CodeDisplay More
Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long, Pos As Long, i As Long Dim CellValue As String, EndChars As String Dim FindChars() As Variant FindChars = Array("-S", "-M", "-09", "-10") With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Application.EnableEvents = False For i = 1 To LastRow CellValue = .Cells(i, "A").Value Pos = InStrRev(CellValue, "-") If Pos > 0 Then EndChars = Mid(CellValue, Pos, 99) If IsNumeric(WorksheetFunction.Match(EndChars, FindChars, 0)) Then .Cells(i, "A").Value = Left(CellValue, Pos - 1) End If End If Next i Application.EnableEvents = True End With End Sub
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.
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
Do you know what's wrong?
Thanks everyone for the quick + helpful replies!
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?