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

jg2703 · Apr 9th 2016

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:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim Pos As Long
    Dim i As Long
    With ActiveSheet
    
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRow
             
            Pos = InStrRev(.Cells(i, "A").Value, "-")
            If Pos > 0 Then
                 
                .Cells(i, "A").Value = Left(.Cells(i, "A").Value, Pos - 1)
            End If
        Next i
    End With
End Sub

Display More

Thanks in anticipation.
- jg2703

skywriter · Apr 9th 2016

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.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim Pos As Long
    Dim i As Long
    With ActiveSheet
         If Target.Column <> 1 Then Exit Sub
         Application.EnableEvents = False
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRow


            Pos = InStrRev(.Cells(i, "A").Value, "-")
            If Pos > 0 Then


                .Cells(i, "A").Value = Left(.Cells(i, "A").Value, Pos - 1)
            End If
        Next i
        Application.EnableEvents = True
    End With
End Sub

Display More

Fuzz-Head · Apr 10th 2016

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..

Code

Sub Testing()


Dim lRow As Long, lChar As Long
Dim vData() As Variant, vPaste() As Variant
Dim sTest As String


With ActiveSheet
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row
        If lRow < 2 Then Exit Sub
        
    vData = .Range("A2").Resize(lRow, 1).Value
        ReDim vPaste(1 To UBound(vData, 1), 1 To UBound(vData, 2))
        
            For lRow = LBound(vData, 1) To UBound(vData, 1)
                    For lChar = Len(vData(lRow, 1)) To 1 Step -1
                        sTest = Mid(vData(lRow, 1), lChar, 1) & sTest
                            If Mid(vData(lRow, 1), lChar, 1) = "-" Then GoTo Jumper
                    Next lChar
Jumper:
                vPaste(lRow, 1) = sTest
                sTest = vbNullString
            Next lRow
    
    .Range("A2").Resize(UBound(vData, 1), 1) = vPaste
End With


End Sub

Display More

**Batman** · Apr 10th 2016

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.

Code

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

Display More

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.

jg2703 · Apr 10th 2016

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

Quote from Batman;768930
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.
Code
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
Display More
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.
Display More

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?

**Batman** · Apr 10th 2016

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.

Code

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, intMatchPos As Integer
    
    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)
                
                intMatchPos = -99
                On Error Resume Next
                intMatchPos = WorksheetFunction.Match(EndChars, FindChars, 0)
                On Error GoTo 0
                
                If intMatchPos >= 0 Then
                    .Cells(i, "A").Value = Left(CellValue, Pos - 1)
                End If
            End If
        Next i
        
        Application.EnableEvents = True
    End With
End Sub

Display More

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.

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

Participate now!

Share