CLEAN function - macro??

[George Applegate]

Can anyone tell me how I could write a macro that would do a "clean"
on every cell in a spreadsheet (you know, strip out unprintable
characters, etc.)?

I can do a "clean" on an individual cell, but would like to be able to
do it for a range of cells...could anyone assist me or point me in the
right direction to do this? Say I want to run the "clean" function on
cells A1 - Z99999, for instance.

I suppose it would take to long to do a CLEAN on every single cell in
the spreadsheet.

But how do I go about writing this macro, and then, once written, can
I attach it to a key or something?

Woudl appreciate any suggestions or help anyone would be willing to
give,

thanks,
ga

George Applegate
(e-mail address removed)

 

[Gord Dibben]

I understand "unprintable" but what are "etc."

Maybe David McRithchie's TRIMALL macro will do the job.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Gord Dibben MS Excel MVP

 

[smartin]

Can anyone tell me how I could write a macro that would do a "clean"
on every cell in a spreadsheet (you know, strip out unprintable
characters, etc.)?

I can do a "clean" on an individual cell, but would like to be able to
do it for a range of cells...could anyone assist me or point me in the
right direction to do this? Say I want to run the "clean" function on
cells A1 - Z99999, for instance.

I suppose it would take to long to do a CLEAN on every single cell in
the spreadsheet.

But how do I go about writing this macro, and then, once written, can
I attach it to a key or something?

Woudl appreciate any suggestions or help anyone would be willing to
give,

thanks,
ga

George Applegate
(e-mail address removed)

Here's a sub I wrote a while back that should help you along. If you
already have a "clean routine" just plug it in where indicated near the
end. Place this in a standard module.

' code start -------------------------------------------------------
Sub DoTrim()
' applies TRIM() to non-formula selected cells
Dim c As Range
Dim Response As Integer
' uncomment next line to auto select used range
'ActiveSheet.UsedRange.Select
If Selection.Cells.Count > 10000 Then
If MsgBox("Warning - this could take a long time. Continue?", _
vbYesNoCancel Or vbDefaultButton2, _
"Trim Cells") _
<> vbYes Then
Exit Sub
End If
End If
Application.ScreenUpdating = False
For Each c In Selection.Cells
If Not c.HasFormula Then
' your favorite code clean routine goes here
c.Value = Trim(c.Value)
End If
Next
Application.ScreenUpdating = True
End Sub
' code end ---------------------------------------------------------


To establish a hot key you can use the macro editor, but this is limited
to Ctrl + key. My personal preference is to use Application.OnKey. I
have the following e.g. set up in an add-in, ThisWorkbook module:

' code start -------------------------------------------------------
Private Sub Workbook_Open()
Application.OnKey "^+a", "AccountingFormat" ' ctrl+shift+a
End Sub
' code end ---------------------------------------------------------

 

[George Applegate]

Thanks for posting your code...if you wouldn't mind elaborating a
bit...on some questions, I'd appreciate it.

I took your routine and made this (plagiarized, I confess, but I'm not
running for political office...):

Sub DOClean()
' applies CLEAN() to non-formula selected cells
Dim c As Range
Dim Response As Integer
' uncomment next line to auto select used range
'ActiveSheet.UsedRange.Select
If Selection.Cells.Count > 10000 Then
If MsgBox("Warning - this could take a long time. Continue?",
_
vbYesNoCancel Or vbDefaultButton2, _
"Clean Cells") _
<> vbYes Then
Exit Sub
End If
End If
Application.ScreenUpdating = False
For Each c In Selection.Cells
If Not c.HasFormula Then
' your favorite code clean routine goes here
c.Value = Clean(c.Value)
End If
Next
Application.ScreenUpdating = True
End Sub

I went into developer tools, then code, visual basic and added this to
"General", is that where I should do it?

Unfortunately it doesn't work though. Maybe it knows this is your code
and not mine - just kidding. But I get an error with Microsoft visual
basic, says "sub or function not defined" and highlights the Clean( in
front of the c.value near the bottom). The clean function is an excel
function but maybe not recognized in VB???

And maybe trim is a function in both excel, and visual basic, but
clean isn't? So maybe I can't do it this way after all.

Also, don't want to sound stupid but am I entering the code the right
way (developer tools, code, visual basic, general? And once this is
written, how do I get it to show up as an "add-in" in other
spreadsheets so I can run it? Can I create it as an add-in somehow?

Thanks much for your help and suggestions. Very much appreciated.

Here's a sub I wrote a while back that should help you along. If you
already have a "clean routine" just plug it in where indicated near the
end. Place this in a standard module.

' code start -------------------------------------------------------
Sub DoTrim()
' applies TRIM() to non-formula selected cells
Dim c As Range
Dim Response As Integer
' uncomment next line to auto select used range
'ActiveSheet.UsedRange.Select
If Selection.Cells.Count > 10000 Then
If MsgBox("Warning - this could take a long time. Continue?", _
vbYesNoCancel Or vbDefaultButton2, _
"Trim Cells") _
<> vbYes Then
Exit Sub
End If
End If
Application.ScreenUpdating = False
For Each c In Selection.Cells
If Not c.HasFormula Then
' your favorite code clean routine goes here
c.Value = Trim(c.Value)
End If
Next
Application.ScreenUpdating = True
End Sub
' code end ---------------------------------------------------------


To establish a hot key you can use the macro editor, but this is limited
to Ctrl + key. My personal preference is to use Application.OnKey. I
have the following e.g. set up in an add-in, ThisWorkbook module:

' code start -------------------------------------------------------
Private Sub Workbook_Open()
Application.OnKey "^+a", "AccountingFormat" ' ctrl+shift+a
End Sub
' code end ---------------------------------------------------------

George Applegate
(e-mail address removed)

 

[smartin]

George, if you continue to plagiarize my code I /expect/ to see your
name on the ballot (^: I am just kidding, of course -- you can do
anything you want with the code.

Now, I don't want to sound stupid either, but I am ignorant of the
"developer tools" pathway -- is that Excel 2007 speak? I am happily
stuck in 2003 so here is a way to install the code that should work in
any 21st century version of Excel. Apologies if these instructions seem
trivial, I'm just trying to cater to unknown levels of familiarity --
including my own.

Create a new, blank workbook. Press Alt+F11. This opens the VBA editor.
Note the "Project" browser is on the left (unless you have already
rearranged things), with your current workbook name highlighted. Go to
Insert | Module. A nice big white space opens up, and "Module1" appears
in the Project browser. Paste the code, your version, into the white space.

Ok I think you probably got this far already, but the code fails the
call to Clean(). Yes, Trim() is a native VBA function, and no, Clean()
is not. But Clean() is a worksheet function, and I believe it is the one
you want to use, so I think you can just replace that line with

c.Value = Application.WorksheetFunction.Clean(c.Value)

Try 'er again. Good? Good.

If you still want that hot key, add the last bit of code I posted to the
"ThisWorkbook" module. Double click "ThisWorkbook" in the Project
browser and paste in the code. You will of course want to change the
specifics to call DOClean using your favorite keystroke. Place the
cursor on the word "OnKey" and hit F1 for all the details.

Lastly, toggle back to the worksheet with Alt+F11 and file | save as | ,
change the type to add-in, name it and save it. Now you have your add-in
that contains the DoClean code and a hot key assignment to call it at
any time. Don't forget to add your add-in in tools | add-ins.

 

[George Applegate]

Excellent, excellent instructions!! Thanks a million!

A couple more questions, if I may. You answer several and that leads
to a couple more...

First, to answer yours, yes, the developer tools is excel 2007. I am
not fond of excel 2007; the color schemes confuse the heck out of me
and I can't ever get them set the way I want; the biggest advantage to
it is the increased number of rows, otherwise I'd probably have stayed
with 2003. I'm not much of an excel guru at all, but am a programmer
by trade on another platform so can usually figure things out like
this - with help from a real "techie" like you! THANKS!!! And even
if I plagiarize, he will know I got this from a kind soul and I didn't
write it myself.

Your instructions were great, outside of I had to get there through
the developer tab. Also, the "Application.worksheetfuntion.clean
corrected the problem I had too, was able to do the onkey code, and I
think everything is going to work.

But one other stupid question...since I haven't tested this yet (I
have to find some bogus data to do so with). Anyway, based on how I
read what you wrote, I would SELECT the range in my spreadsheet of
waht I want to run the DOCLEAN on and then once it's selected, do my
onkey to run it?? And it will run it on that range, correct? Is that
what the "Dim c As Range" does or is it the "If Selection.cells.count"
that makes it know I am working with only a selected cell area??? If
I wanted to arbitrarily do the entire sheet, then I could uncomment
the "ActiveSheet.UsedRange.Select" based on your documentation...
If I am interpreting your code, the "selection area" becomes "c" and
then the range "c" is what is processed???

Anyway, I have the code entered, saved as an add-in, and actually even
have it load now when I open excel and I I have it as a "control"
which I can just click on in my excel 2007 toolbar. Pretty slick.

Thanks so much, I think I will be able to get it the rest of the way
but I certainly couldn't have done it without your detailed
instructions!

George, if you continue to plagiarize my code I /expect/ to see your
name on the ballot (^: I am just kidding, of course -- you can do
anything you want with the code.

Now, I don't want to sound stupid either, but I am ignorant of the
"developer tools" pathway -- is that Excel 2007 speak? I am happily
stuck in 2003 so here is a way to install the code that should work in
any 21st century version of Excel. Apologies if these instructions seem
trivial, I'm just trying to cater to unknown levels of familiarity --
including my own.

Create a new, blank workbook. Press Alt+F11. This opens the VBA editor.
Note the "Project" browser is on the left (unless you have already
rearranged things), with your current workbook name highlighted. Go to
Insert | Module. A nice big white space opens up, and "Module1" appears
in the Project browser. Paste the code, your version, into the white space.

Ok I think you probably got this far already, but the code fails the
call to Clean(). Yes, Trim() is a native VBA function, and no, Clean()
is not. But Clean() is a worksheet function, and I believe it is the one
you want to use, so I think you can just replace that line with

c.Value = Application.WorksheetFunction.Clean(c.Value)

Try 'er again. Good? Good.

If you still want that hot key, add the last bit of code I posted to the
"ThisWorkbook" module. Double click "ThisWorkbook" in the Project
browser and paste in the code. You will of course want to change the
specifics to call DOClean using your favorite keystroke. Place the
cursor on the word "OnKey" and hit F1 for all the details.

Lastly, toggle back to the worksheet with Alt+F11 and file | save as | ,
change the type to add-in, name it and save it. Now you have your add-in
that contains the DoClean code and a hot key assignment to call it at
any time. Don't forget to add your add-in in tools | add-ins.

George Applegate
(e-mail address removed)

 

[smartin]

Great! Glad you got it all down. Here's the rest of the story...

But one other stupid question...since I haven't tested this yet (I
have to find some bogus data to do so with). Anyway, based on how I
read what you wrote, I would SELECT the range in my spreadsheet of
waht I want to run the DOCLEAN on and then once it's selected, do my
onkey to run it?? And it will run it on that range, correct?
Exactly.

Is that
what the "Dim c As Range" does or is it the "If Selection.cells.count"
that makes it know I am working with only a selected cell area???

No. In this context, "Selection" is effectively a property of the active
worksheet. I.e., whatever is selected in the active worksheet can be
interrogated via the Selection property. This allows us for example to
learn the count of selected cells in the worksheet, or create a one cell
range within that selection.

If you uncomment 'ActiveSheet.UsedRange.Select that selection is changed.

If
I wanted to arbitrarily do the entire sheet, then I could uncomment
the "ActiveSheet.UsedRange.Select" based on your documentation...

Basically. But note that the entire sheet is not processed, just the
rectangular range defined by the extremities where you actually have
data. That's what "UsedRange" returns.

If I am interpreting your code, the "selection area" becomes "c" and
then the range "c" is what is processed???

Sort of. Variable c is initially an empty range object. It actually has
no substance until the "For Each" loop fires. At that point, c assumes a
range of one cell at a time as For Each walks through the list of cells
in the selection. This is what I meant by "create a one cell range
within that selection".

Happy coding!

 

[George Applegate]

Thanks much for your explanations, help me understand it much better.

I did make one modification to my routine. After the:
c.Value = Application.WorksheetFunction.Clean(c.Value)
I added:
c.Value = Trim(c.Value)

It appears to work - i.e. do both of these at the same time...first
clean, then trim...is that acceptable?

One other question. Unfortunately, the "clean" didn't do exactly what
I wanted. Here's the thing. Awhile back we copied some data off a
web-page and imported it into excel - it brought along some characters
that you couldn't see on the screen - like a y with "umlauts' over it
- ascii 255??? Anyway, I would like to use this subroutine you've been
so kind enough to share with me to somehow clean out non-displayable
characters or abnormal characters. The way I got rid of these the
first time was to do a find/replace, but it'd sure be nice to be able
to "clean" cells of characters like this that are say over
ascii-128...but then that will take out 1/4, 1/2, 3/4, etc; so maybe
beyond hex-191??? Is that doable?? Clean only does ascii's (or
unicodes of 0-31, but the problem we had, and what I was trying to do
was also get rid of junk codes like ascii-255...the y with the ".."
over the top.

Have you ever tried to do something like this? Or I am I just stuck
with find/replace. Problem is, when we copied in the web-page stuff,
it didn't even display that "y" with the ".." on top of it
characters... so we didn't even realize they were there!!! Any
suggestions???

I must say, you've been a TREMENDOUS HELP!!

Thanks again smartin (don't know your first name!!)
ga

Great! Glad you got it all down. Here's the rest of the story...


No. In this context, "Selection" is effectively a property of the active
worksheet. I.e., whatever is selected in the active worksheet can be
interrogated via the Selection property. This allows us for example to
learn the count of selected cells in the worksheet, or create a one cell
range within that selection.

If you uncomment 'ActiveSheet.UsedRange.Select that selection is changed.


Basically. But note that the entire sheet is not processed, just the
rectangular range defined by the extremities where you actually have
data. That's what "UsedRange" returns.


Sort of. Variable c is initially an empty range object. It actually has
no substance until the "For Each" loop fires. At that point, c assumes a
range of one cell at a time as For Each walks through the list of cells
in the selection. This is what I meant by "create a one cell range
within that selection".

Happy coding!

George Applegate
(e-mail address removed)

 

[smartin]

Hi again.

Copy/paste from the web is dicey in my experience. Lots of, er, unusual
characters and formatting get inherited this way. As a suggestion,
instead of a straight copy/paste, try copy/paste special | values.

But you already have data with strange characters, so let's deal with
them. Since the built-in "Clean" routine isn't up to the job, let's make
our own. There's no need to get stuck now.

Here is a new function you should add to your add-in code module, e.g.,
below Sub DoClean. All this does is loop through each character in the
input, keep the ones that fit in our desired ASCII range, and discard
the rest.

' code begin ----------------------------------------
Function MyClean(StringIn As String) As String
' remove characters with ASCII value < 32 or > 191
Dim i As Long
Dim c As String
Dim L As Long
Dim tmps As String

L = Len(StringIn)
For i = 1 To L
c = Mid(StringIn, i, 1)
If Asc(c) >= 32 And Asc(c) <= 191 Then
tmps = tmps & c
' uncomment below to substitute undesirables
' with a fixed character
'Else
' tmps = tmps & "."
End If
Next i
MyClean = tmps
End Function
' code end ------------------------------------------


I'm sure some hard core programmers are cursing me at this point, but
I'm lazy and use brief variable names for routine assignments...

Back in DoClean, replace
c.Value = Application.WorksheetFunction.Clean(c.Value)

with
c.Value = MyClean(c.Value)

Oh, and yes, it is perfectly acceptable to apply as many functions to
c.Value as you care to do. In fact, it is probably a /good idea/ to keep
the calls separate in the interest of keeping the code modular.

The name's Andy, glad to meet you.

 

[George Applegate]

Andy,

You make this stuff look too easy...and I know better. Thanks much
for the ascii code and although I haven't had a chance to test it, I
suspect it will work great!

Thanks a million!
ga

Hi again.

Copy/paste from the web is dicey in my experience. Lots of, er, unusual
characters and formatting get inherited this way. As a suggestion,
instead of a straight copy/paste, try copy/paste special | values.

But you already have data with strange characters, so let's deal with
them. Since the built-in "Clean" routine isn't up to the job, let's make
our own. There's no need to get stuck now.

Here is a new function you should add to your add-in code module, e.g.,
below Sub DoClean. All this does is loop through each character in the
input, keep the ones that fit in our desired ASCII range, and discard
the rest.

' code begin ----------------------------------------
Function MyClean(StringIn As String) As String
' remove characters with ASCII value < 32 or > 191
Dim i As Long
Dim c As String
Dim L As Long
Dim tmps As String

L = Len(StringIn)
For i = 1 To L
c = Mid(StringIn, i, 1)
If Asc(c) >= 32 And Asc(c) <= 191 Then
tmps = tmps & c
' uncomment below to substitute undesirables
' with a fixed character
'Else
' tmps = tmps & "."
End If
Next i
MyClean = tmps
End Function
' code end ------------------------------------------


I'm sure some hard core programmers are cursing me at this point, but
I'm lazy and use brief variable names for routine assignments...

Back in DoClean, replace
c.Value = Application.WorksheetFunction.Clean(c.Value)

with
c.Value = MyClean(c.Value)

Oh, and yes, it is perfectly acceptable to apply as many functions to
c.Value as you care to do. In fact, it is probably a /good idea/ to keep
the calls separate in the interest of keeping the code modular.

The name's Andy, glad to meet you.

George Applegate
(e-mail address removed)

 

[George Applegate]

Smartin, are you there?

awhile back you helped me with creating a DOCLN add-in for excel.

In excel 2007 I can add that add-in to the most used commands or quick
access toolbar.

How do I add this add-in to a tool-bar in excel??? Or do I have to
associate it with a macro key in order to do so??? In some of your
code you had some examples of how to attach it to a key combination,
but I'd prefer for the user to be able to highlight the cells they
want to clean, and then click a toolbar item to do the clean.

Or is that not possible in excel 2003??? This is for excel 2003...

Thanks for your help

Great! Glad you got it all down. Here's the rest of the story...


No. In this context, "Selection" is effectively a property of the active
worksheet. I.e., whatever is selected in the active worksheet can be
interrogated via the Selection property. This allows us for example to
learn the count of selected cells in the worksheet, or create a one cell
range within that selection.

If you uncomment 'ActiveSheet.UsedRange.Select that selection is changed.


Basically. But note that the entire sheet is not processed, just the
rectangular range defined by the extremities where you actually have
data. That's what "UsedRange" returns.


Sort of. Variable c is initially an empty range object. It actually has
no substance until the "For Each" loop fires. At that point, c assumes a
range of one cell at a time as For Each walks through the list of cells
in the selection. This is what I meant by "create a one cell range
within that selection".

Happy coding!

George Applegate
(e-mail address removed)