CODEWITHSUNDEEP
vbaexcel
xyz
xyz

Reputation: 2300

Code using .address not executing code, though it runs without error

I can not figure out what is missing from the Sub RemoveC

The function works fine as I can call it for a single column but I want to run it on entire sheets and address is pretty fast

The code runs but nothing happens , not sure what I need to change so that my use of .address works

Thanks

Sub RemoveC()
Dim ws As Worksheet

For Each ws In Worksheets(Array("X", "Y"))
    With ws.UsedRange.Offset(1, 0)

       removeChars " & .Address & "

    End With
Next ws
End Sub

Function:

Function removeChars(ByVal strSource As String) As String
Dim strResult As String
Dim i As Long

For i = 1 To Len(strSource)
    Select Case Asc(Mid(strSource, i, 1))
        Case 0, 9, 10, 12, 33, 48 To 57, 126 To 255:

        Case Else:
            strResult = strResult & Mid(strSource, i, 1)

    End Select
Next i

removeChars = strResult

End Function

Upvotes: 0

Views: 34

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

in order to have the function apply to the content of the cell, change removeChars " & .Address & " to .Value = removeChars(.Text)

Upvotes: 1

Related Questions