VB Script
 

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!

--
Krista

VB Script
 

See

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

In article ,
Krista wrote:

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

VB Script
 

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


"JE McGimpsey" wrote:

See

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

In article ,
Krista wrote:

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

VB Script
 

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






In article ,
Krista wrote:

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!

VB Script
 

Thanks much! Have a great weekend!

--
Krista


"JE McGimpsey" wrote:

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






In article ,
Krista wrote:

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!