Using clean on a single column not a whole worksheet 1

[woodyinoz]

Hi all,

I'm looking to use the clean command on a single column rather than a whole worksheet. Can I do this?

I've got the following code so far:

Sub cleanup()
Dim TheCell As Range

For Each TheCell In ActiveSheet.UsedRange
    With TheCell
    If .HasFormula = False Then
        .Value = Application.WorksheetFunction.Clean(.Value)
    End If
    End With
Next TheCell
End Sub
[\Code]

This changes the whole worksheet.
I am trying to remove all cr and nl from my worksheet but they all appear in the same column. The problem with running the code on the whole worksheet is that it also removes leading zeros which I don't want to happen!

Any ideas anyone?

Cheers,

Woody

 

[xlbo]

and this....

For Each TheCell In ActiveSheet.UsedRange.columns(1)

change 1 to be the column you want to clean up

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[woodyinoz]

This is probably something really obvious but now I get an error on:

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

Cheers for the help Geoff...

Woody

 

[xlbo]

try using either Application Or Worksheetfunction - don't need both

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[woodyinoz]

Geoff,

I've tried both... The error is the same 'Type Mismatch'.

Any other ideas?

 

[xlbo]

please post all the code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[woodyinoz]

Here you go... I'm trying to do a clean on column M only.

Sub cleanup()
Dim TheCell As Range

For Each TheCell In ActiveSheet.UsedRange.Columns(13)
    With TheCell
    If .HasFormula = False Then
        .Value = Application.WorksheetFunction.Clean(.Value)
    End If
    End With
Next TheCell
End Sub

Cheers

Woody

 

[xlbo]

you must use .address instead of .value - just like you would if you used the function on the spreadsheet itself

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

apologies - scrath that. The reason it is erroring is that it is trying to use the whole column in one go. Change to:

Sub cleanup()
Dim TheCell As Range

For Each TheCell In ActiveSheet.UsedRange.Columns(13)[b].cells[/b]
    With TheCell
    If .HasFormula = False Then
        .Value = Application.WorksheetFunction.Clean(.Value)
    End If
    End With
Next TheCell
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[woodyinoz]

Fantastic.... Thanks very much.