Clean Function Multiple Columns

[NYFashionToGo]

I have been trying to figure this out, I have 2 clean fuctions One cleans The whole sheet. and The second cleans Just once single column. How would I alter this to clean a few columns (Not all) But not one:


Cleans All Cells
Sub cleanup1()
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



Cleans One Column (Column C)

Sub cleanup()
Dim TheCell As Range
'The Number 3in the next row is the column number.
For Each TheCell In ActiveSheet.UsedRange.Columns(3).Cells

With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell

End Sub

I tried Adding (3),(4),(5) a number of different ways and it did not work. I dont know what I am doing wrong..

Can anyone assist? Thanks

 

[Andrzejek]

How about:

Sub cleanup([b]intRow[/b])
Dim TheCell As Range
'The Number 3in the next row is the column number.
For Each TheCell In ActiveSheet.UsedRange.Columns([b]intRow[/b]).Cells
    With TheCell
    If .HasFormula = False Then
        .Value = Application.WorksheetFunction.Clean(.Value)
    End If
    End With
Next TheCell

End Sub

But you would need to pass which column you need to clean:
Call cleanup(4)



Have fun.

---- Andy

 

[SkipVought]

Hi,

Pass a From, Thru ColNbr

Sub cleanup(nFrom as integer, nThru as integer)
Dim iCol as integer
'The Number 3in the next row is the column number.
For icol = nfrom to nthru

    With With Cells(activesheet.usedrange.row,iCol)
      If .HasFormula = False Then
        .Value = Application.WorksheetFunction.Clean(.Value)
      End If
    End With
Next 

End Sub

Skip,

_[red][/red]