Clear extra characters from a provided range of cells

[imran1059]

Dear All,

I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when lines are in thousands, Can somebody provide a more efficient code?

Sub ClearExtraCharacters()
Dim rng As Range, cell As Range

On Error Resume Next
Set rng = Application.InputBox("Select Range", "Range Selection", , , , , , 8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub


For Each cell In rng.Cells
cell.Value = AlphaNumericOnly(cell.Value)
Next cell

MsgBox "Fixed", 48, "Fixed"
End Sub

 

[James006]

Hi,

You are using a specific UDF : the AlphaNumericOnly function ... which you should post ...

 

[davesexcel]

AlphaNumericOnly

There a few versions of this function, which one are you using?


It could go a lot faster if you have actual chars you wanted to remove.

The range being selected, if you have 1000's of rws would they be selecting 1000's of cells or would they just be selecting the column?

 

[imran1059]

Sorry guys, here is the function

Function AlphaNumericOnly(strSource As String) As String
Dim i As Integer
Dim strResult As String

For i = 1 To Len(strSource)
Select Case Asc(Mid(strSource, i, 1))
Case 32 To 38, 40 To 62, 64 To 126
strResult = strResult & Mid(strSource, i, 1)
End Select
Next
AlphaNumericOnly = strResult
End Function

 

[imran1059]

There a few versions of this function, which one are you using?


It could go a lot faster if you have actual chars you wanted to remove.

The range being selected, if you have 1000's of rws would they be selecting 1000's of cells or would they just be selecting the column?

It depends, generally I am selecting the range of first 30K to 40K rows with first 10-12 columns

 

[davesexcel]

30k x 10 =300k x #characters in each cell could put you in the millions of loops.

Did you have a thought about the other part of my question?

 

[imran1059]

99% of the times there is an extra apostrophe character in the beginning of the text in any given cell. But in rare cases, there are other unrecognizable characters. As I am in finance so even one missed line may give me a lot of trouble in my reconciliations.

 

[footoo]

Check whether this is quicker :

Sub ClearExtraCharacters()
Dim rng As Range, i%
On Error Resume Next
Set rng = Application.InputBox("Select Range", "Range Selection", , , , , , 8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For i = 33 To 46
    rng.Replace "~" & Chr(i), "", xlPart
Next
For i = 58 To 64
    rng.Replace "~" & Chr(i), "", xlPart
Next
For i = 91 To 96
       rng.Replace "~" & Chr(i), "", xlPart
Next
For i = 123 To 190
    rng.Replace "~" & Chr(i), "", xlPart
Next
MsgBox "Fixed", 48, "Fixed"
End Sub

 

[Peter_SSs]

Check whether this is quicker :

With all those interactions between code and worksheet, ScreenUpdating off might help with speed

 

[footoo]

With all those interactions between code and worksheet, ScreenUpdating off might help with speed

Also, if any event procedures and/or lots of formulas, probably need to disable.