Clear extra characters from a provided range of cells

imran1059

Board Regular
Joined
Sep 28, 2014
Messages
92
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

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

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
767
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

Board Regular
Joined
Sep 28, 2014
Messages
92
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

Board Regular
Joined
Sep 28, 2014
Messages
92
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

Well-known Member
Joined
Feb 26, 2006
Messages
767
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

Board Regular
Joined
Sep 28, 2014
Messages
92
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,660
Check whether this is quicker :
VBA Code:
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
 

Forum statistics

Threads
1,081,855
Messages
5,361,714
Members
400,648
Latest member
dalviaks

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top