CLEAN function - macro??
 

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
c

CLEAN function - macro??
 

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


On Tue, 15 Sep 2009 16:45:45 -0500, 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

CLEAN function - macro??
 

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
c


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 ---------------------------------------------------------