CLEANing up some imported text 1

[mwvR]

I've got some unprintable characters when importing a text report generated from a call handling system. I've got some code to clean using an Excel worksheet function:

'lots of gaps between lines...
cRows = Range("A65536&quot.End(xlUp).Row + 1
Range("B1&quot.Select
Do Until ActiveCell.Row = cRows
ActiveCell.FormulaR1C1 = "=CLEAN(RC1)"
ActiveCell.Offset(1, 0).Select
Loop
Columns("B:B&quot.Copy
Range("A1&quot.PasteSpecial Paste:=xlValues
Columns(2).Delete Shift:=xlToLeft

...but it is way slow! The main unprintable culprit I want to remove is  (chr 12). I originally tried:

Columns(1).Replace What:=Chr(12), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

...but it doesn't comply (get error message "Function Not Valid&quot.

Any help greatly appreciated.

 

[rmikesmith]

mwvr,

Try the following procedure, which uses the CLEAN worksheet function to remove non-printing characters from data in Column A. It differs from your code snippet in several ways: It uses the CLEAN functionality directly, operating on the data to be cleaned, instead of generating a worksheet formula, copying and pasting the generated values back to the data column, etc. It does not select the cells to be operated on; this is slow! It turns screen updating off, which increases the speed of worksheet operations dramatically.

Sub CleanData()
Dim LastRow As Long
Dim i As Long

  Application.ScreenUpdating = False
  With ThisWorkbook.ActiveSheet
    LastRow = .Range("A65536").End(xlUp).Row
    For i = 1 To LastRow
      .Cells(i, 1).Value = Application.Clean(.Cells(i, 1).Value)
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Hope this helps.
M. Smith

 

[mwvR]

Thanks Mike

Using your code on a file (54000 odd rows including blank rows) took around 22 seconds compared to 1m 20 with my code (both with Screenupdating to false). A definite improvement!

Any thoughts on why:
Columns(1).Replace What:=Chr(12), Replacement:=""
...does not work consistently? I'm sure it worked one day for me and not the next!

Malachi

 

[xlbo]

I would guess that you did a manual find / find & replace and changed the settings. If you don't set them in code (ie lookIn = xlwhole or xlpart) it uses the settings of whatever was used previously ie if you were looking for part of a cell originally, it would find chr(12) but if you then do a manual find and look for whole cells, next time you run the code, it'll look for whole cells unless you specify LookIn:= xlpart as part of the code

Hope that makes sense

Rgds
~Geoff~

 

[rmikesmith]

Malachi,

If the Replace method is not working consistently, as you say [i.e. not finding Chr(12) when these are known to exist], then I would go with Geoff's explanation. If you are getting an error message as indicated in your original post, then something else is going on, but I'm not sure what. I tested this method and it worked fine. If you can get this to work and are able to identify all non printing characters, it may well be faster. Cleaning 65536 rows using my CLEAN routine took ~21 seconds while the Replace method took ~2.5 seconds.

Regards,
Mike

 

[mwvR]

Geoff/Mike

Thanks both for your responses.

In regards to Find&Replace, I used the exact code:

Columns(1).Replace What:=Chr(12), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

...but get Run-Time 1004 "Function not Valid". Because the main culprit is Chr(12) this line would be preferable for speed but it just won't run. I thought it might have something to do with using the code on an imported text file but it made not difference after moving the data to a saved Excel file. I'll stick with Mike's code unless you have any other thoughts on why Find&Replace might not work?

Thanks, Malachi

 

[xlbo]

Try it in a SUB instead of a function - AFAIK, functions should return values rather than perform actions Rgds
~Geoff~

 

[mwvR]

Geoff

The code is in a SUB though the error message does say "That function is not valid". I separated out the single line to a separate SUB, ran it, and still receive the error message.

Sub findandReplaceCHR12()
Columns(1).Replace What:=Chr(12), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

I'm not going to worry too much as Mike's code does the trick, it's just a little annoying...

Malachi

 

[xlbo]

Weird - I copied your code into a sub and ran it - worked fine
Last thought - is the sheet you're doing the Find and Replace active when you are trying to run the sub ??
try referencing the sheet:
Activesheet.columns..... or
Sheets("Sheetname&quot.columns...... Rgds
~Geoff~

 

[mwvR]

Geoff

Tried both still no joy. Not to worry, and thanks for taking the time to have a look.

Malachi