CODEWITHSUNDEEP
excelvba
Nic
Nic

Reputation: 127

Looping through the rows until last row

Hello I am new to VBA and I have an excel sheet as shown in the image below and I want to run through each row until the last row to remove the characters in front of "/".

Excel Sheet Example

The following is my VBA code. However, my code could only work on one cell currently and I am not sure how do I change it to a For Loop to run through all the rows until the last row. Also, there are certain cells that does not contain the "/" and I just want it to be an empty cell. I need some help on how could I work on this? Thank you really appreciate if anyone would be able to assist me with this.

Sub String()
' String
Dim stringOriginal As String
stringOriginal = Range("A2").Value

Dim indexOfThey As Integer
indexOfThey = InStr(1, stringOriginal, "/")

Dim finalString As String
finalString = Right(stringOriginal, Len(stringOriginal) - indexOfThey)

Range("A2").Value = finalString

End Sub

Upvotes: 0

Views: 4203

Answers (1)

Harun24hr
Harun24hr

Reputation: 36870

Try following sub

Sub StringOperation()
Dim lRow As Long
Dim Rng As Range

lRow = Cells(Rows.Count, 1).End(xlUp).Row

For Each Rng In Range("A1:A" & lRow)
    If (InStr(1, Rng, "/")) > 0 Then
        Rng = Right(Rng, Len(Rng) - InStr(1, Rng, "/"))
    End If
Next

End Sub

Edit: To empty cells that doesn't contain / use below codes.

Sub StringOperation()
Dim lRow As Long
Dim Rng As Range

lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For Each Rng In Range("A1:A" & lRow)
        If (InStr(1, Rng, "/")) > 0 Then
            Rng = Right(Rng, Len(Rng) - InStr(1, Rng, "/"))
              Else
            Rng = ""
        End If
    Next
End Sub

Upvotes: 1

Related Questions