in need of VBA to locate and delete NON-PRINTING characters

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

in need of VBA to locate and delete NON-PRINTING characters

Post by bradjedis »

Greetings.

I have exceedingly large one mil + lines of data spreadsheet. In a couple of columns I have data that could contain NON printing characters... IE carriage returns, etc..

I am in need of a an on demand macro that will search and remove them if they exist in a selected column.


Thoughts?

Thanks,
Brad

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: in need of VBA to locate and delete NON-PRINTING characters

Post by HansV »

Does this do what you want? Please test on a copy of your worksheet first.

Code: Select all

Sub RemoveNonPrintable()
    Dim r As Object
    Dim v As Variant
    Dim i As Long
    Dim j As Long
    Set r = CreateObject("VBScript.RegExp")
    r.Global = True
    r.Pattern = "[^ -ÿ]+"
    v = Selection.Value
    For i = 1 To UBound(v, 1)
        For j = 1 To UBound(v, 2)
            v(i, j) = r.Replace(v(i, j), "")
        Next j
    Next i
    Application.ScreenUpdating = False
    Selection.Value = v
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: in need of VBA to locate and delete NON-PRINTING characters

Post by bradjedis »

That appears to work!

Thanks...