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
in need of VBA to locate and delete NON-PRINTING characters
-
- 4StarLounger
- Posts: 544
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78626
- 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
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
Hans
-
- 4StarLounger
- Posts: 544
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: in need of VBA to locate and delete NON-PRINTING characters
That appears to work!
Thanks...
Thanks...