VBA Loop that removes punctuation from a given range

Admin 1
Viewed 81

I am pretty new to VBA, and i need to make a loop that removes certain punctuation which is referenced as values A2:A33 on my spreadsheet, from a string. This is what I have so far, struggling with the replace function. Any help would be greatly appreciated thanks!

Function CleanDescription(rawDescription As String) As String
Dim punctuationRng As Range
Set punctuationRng = Worksheets("ToRemove").Range("A2:A33")

Dim cleanDesc As String
cleanDesc = rawDescription

For i = 1 To punctuationRng.Count
cleanDesc = Replace(cleanDesc, punctuationRng, "")
Next i

CleanDescription = cleanDesc
End Function
vba excel loops
2 Answers

You can achieve substituting each value in a range of cells that is present in a string by a For Each:

Function CleanDescription(rawDescription As String) As String
Dim punctuationRng As Range
Set punctuationRng = Worksheets("ToRemove").Range("A2:A33")

Dim cleanDesc As String
cleanDesc = rawDescription

For Each punctuationCell In punctuationRng
    cleanDesc = Replace(cleanDesc, punctuationCell.Value, "")
Next punctuationCell

CleanDescription = cleanDesc
End Function

If you don't like the For Each you could also achieve it with a For Loop:

Dim cellValue As String
For r = 1 to punctuationRng.Rows.Count
    For c = 1 to punctuationRng.Columns.Count
        cellValue = punctuationRng.cells(r, c).Value
        cleanDesc = Replace(cleanDesc, cellValue, "")
    Next c
Next r
user_35646320

You can use a For Each Loop as shown below

Function CleanDescription(rawDescription As String) As String
    Dim punctuationRng As Range
    Set punctuationRng = Worksheets("ToRemove").Range("A2:A33")

    Dim cleanDesc As String
    cleanDesc = rawDescription

    Dim aCell As Range

    For Each aCell In punctuationRng
        cleanDesc = Replace(cleanDesc, aCell.Value2, "")
    Next aCell

    CleanDescription = cleanDesc
End Function
user_11405790
Related