Looping through the rows until last row

Question

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

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

Does this answer your question? [Iterating through populated rows in Excel using VBA](https://stackoverflow.com/questions/24377197/iterating-through-populated-rows-in-excel-using-vba) — braX, Jul 03 '20 at 06:56
@braX Hello the example you gave consists of rows and columns but I just need it to iterate through a specific column how would I be able to do that? — Nic, Jul 03 '20 at 06:58
There are plenty of other examples here. Did you try searching for it yourself first? — braX, Jul 03 '20 at 07:14
And if you want VBA code then you can simply record a macro using Text To Columns. BTW From ABC/DEF what do you want? ABC or DEF? For 123, I guess you want a Blank? — Siddharth Rout, Jul 03 '20 at 09:18

Harun24hr · Accepted Answer · 2020-07-03T08:11:19.983

1

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

edited Jul 03 '20 at 08:11

answered Jul 03 '20 at 07:06

Harun24hr

30,391
4
21
36

Hello I would like to make the cell empty if the original one doesn't contain a "/" character how would I be able to do that? – Nic Jul 03 '20 at 07:17
That's what his example shows you... see how he is using the `Instr` function? – braX Jul 03 '20 at 08:01
@JinWei You just need to add `Else` branch to `IF` statement like `Else Rng = ""`. See edited answer. – Harun24hr Jul 03 '20 at 08:13
Alright thank you. Another question would be if I want to add "Test" infront of the current string that I have how do I do that? And it will still be empty for those empty cells. – Nic Jul 03 '20 at 08:47
@JinWei Then change this line `Rng = Right(Rng, Len(Rng) - InStr(1, Rng, "/"))` to `Rng = "Test" & Right(Rng, Len(Rng) - InStr(1, Rng, "/"))` – Harun24hr Jul 03 '20 at 09:09
@JinWei If you found this answer useful then consider accepting it. Tick as green. Also `Upvote` encourage users. – Harun24hr Jul 03 '20 at 09:11
Hello I would like to ask if I have 2 columns with values (13-15 JULY, 3-4 AUG) I would like to change it to just 13-07-2020 and 03-08-2020 how do I do that? – Nic Jul 03 '20 at 09:30
@JinWei Please ask an separate question with your sample data and expected output. This can be done with excel formula and no need `VBA`. – Harun24hr Jul 03 '20 at 09:33

Looping through the rows until last row

1 Answers1

Linked