VB Script

[Guest]

I'm not too familiar with VB scripts in Excel and I received one that I want
to run, but please forgive my ignorance, how to I run it? Where to I input
the script?

(The VB script removes all carriage returns and tabs in a workbook.)

Thanks!

 

[JE McGimpsey]

See

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks! This helped to run the VB Script; however, the script isn't doing
what it's suppose to, which is remove all tabs and carriage returns within a
workbook.

Here's the script:

Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormulas = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub

Any suggestions as to what might be wrong? Like I previously said ... I
don't know VB at all and am just trying to run a script that I got from
someone else.

Thanks!

 

[JE McGimpsey]

Well, the macro shouldn't run - you'd need to delete the "s" in
".HasFormulas"

An alternative, which may be faster on a large sheet with many formulae:

Public Sub CleanUp2()
Dim rConstants As Range
Dim rCell As Range
On Error Resume Next 'in case no constant cells
Set rConstants = _
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConstants Is Nothing Then
For Each rCell In rConstants
With rCell
.Value = Application.WorksheetFunction.Clean(.Value)
End With
Next rCell
End If
End Sub

 

[Guest]

Thanks much! Have a great weekend!

--
Krista

Well, the macro shouldn't run - you'd need to delete the "s" in
".HasFormulas"

An alternative, which may be faster on a large sheet with many formulae:

Public Sub CleanUp2()
Dim rConstants As Range
Dim rCell As Range
On Error Resume Next 'in case no constant cells
Set rConstants = _
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConstants Is Nothing Then
For Each rCell In rConstants
With rCell
.Value = Application.WorksheetFunction.Clean(.Value)
End With
Next rCell
End If
End Sub