Macro to lowercase all letters and remove spaces

[neodjandre]

Hello,

I am looking for a macro to lowercase all letters and remove all spaces from the cells of a column, say column "B1:B500". Could anyone help with this?

thanking you in advance,
Andy

 

[VoG]

Try

Sub lc()
Dim c As Range
For Each c In Range("B1:B500")
    c.Value = Replace(c.Value, " ", "")
    c.Value = LCase(c.Value)
Next c
End Sub

 

[Jon von der Heyden]

Try:

Sub neodjandre()

Dim iCell As Range

    For Each iCell In Selection
        iCell = LCase(Replace(iCell, " ", ""))
    Next iCell

End Sub

This is not set to a specific range, it will apply to whatever range you select on the sheet...

 

[dafan]

Nvm way too long...pro's already answered it.

 

[Richard Schollar]

Here's an alternative:

Sub Conv()
Dim r As Range
Set r = Range("B1:B500")
r.Value = Evaluate("IF(ROW(" & r.Address & "),LOWER(SUBSTITUTE(" & r.Address & ","" "","""")))")
End Sub

 

[jindon]

Another one

Sub test()
Range("B1:B500").Value = [IF(ROW(b1:b500),LOWER(SUBSTITUTE(b1:b500," ","")))]
End Sub

 

[neodjandre]

thanks guys! I will go for the simplest for the time being... but I am sure your other ideas will be useful in the future!