Re: CLEAN function - macro??

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 wrote:
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!

smartin <smartin108@xxxxxxxxx> wrote:

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 wrote:
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.



smartin <smartin108@xxxxxxxxx> wrote:

George Applegate wrote:
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
gappleg8@xxxxxxxxxxxx
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
gappleg8@xxxxxxxxxxxx

George Applegate
gappleg8@xxxxxxxxxxxx
.

Relevant Pages

  • Re: Im pretty sure it was possible
    ... Then Adrien pops in with a reference to Word which has no bearing on Excel. ... I have used Excel for a very long time and was used to the way cell ... For Each Rng In Selection.Cells ...
    (microsoft.public.excel.misc)
  • Re: CLEAN function - macro??
    ... But you already have data with strange characters, ... Since the built-in "Clean" routine isn't up to the job, ... 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. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: QDE (Quick Date Entry)
    ... >>and different languages we didn't catch thus bug. ... My statement was just meant to be that this is another issue with Excel ... > that users must set their Edit options to move selection after enter? ... > tried it out on single cell ranges. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: CLEAN function - macro??
    ... characters and formatting get inherited this way. ... Since the built-in "Clean" routine isn't up to the job, ... interrogated via the Selection property. ... learn the count of selected cells in the worksheet, ...
    (microsoft.public.excel.worksheet.functions)
  • Im pretty sure it was possible
    ... when you make multiple selection while holding ctrl on Word 2007 you can of course unselect by re-clicking... ... So keep your lousy explication (like "I think you may be confusing the behavior in Windows Explorer with Excel") for yourself, ... I have used Excel for a very long time and was used to the way cell selection ... For Each Rng In Selection.Cells ...
    (microsoft.public.excel.misc)