VBA to Remove Leading Space

[Mal4131]

I am not well versed on macros and am looking for assitance on a VBA that would remove/trim leading spaces. Cell range is A1:AU500. I've googled around and searched the boards here, but I can't quite get anything to work. Thanks for your help.

 

[JLGWhiz]

One way

Dim c As Range
For Each c In Range("A1:AU500")
    c = Trim(c.Value)
Next

 

[MARK858]

If it is literally just leading spaces you want removed (and not trailing spaces as well) then a tiny amedment to JLGWhiz's code...

Dim c As Range
For Each c In Range("A1:AU500")
    c = [COLOR="#FF0000"]L[/COLOR]Trim(c.Value)
Next

 

[Mal4131]

Thank you JLGWhiz and Mark858. When attempting to run the VBA I receive the following error message: "Compile error: Invalid outside procedure". Any thoughts? Thanks again.

 

[Rick Rothstein]

Thank you JLGWhiz and Mark858. When attempting to run the VBA I receive the following error message: "Compile error: Invalid outside procedure". Any thoughts? Thanks again.

You need to put the posted code inside a macro housing. If you do not have your own macro to add it to, here is what it would look like as a standalone macro...

Sub TrimLeadingSpaces()
  Dim c As Range
  For Each c In Range("A1:AU500")
    c = LTrim(c.Value)
  Next
End Sub

 

[Mal4131]

Thanks Rick! This works; I thought I was doing this earlier, but must have missed something. Quick question: If I also wanted to trim leading zeros from the same range, would I need a separate macro or could the code be incorporated/ built on the one above? Thanks again for your help.

 

[Rick Rothstein]

Thanks Rick! This works; I thought I was doing this earlier, but must have missed something. Quick question: If I also wanted to trim leading zeros from the same range, would I need a separate macro or could the code be incorporated/ built on the one above? Thanks again for your help.

I think this one macro would handle both situations...

Sub TrimLeadingZerosAndSpaces()
  Dim c As Range
  For Each c In Range("A1:AU500")
    c = Replace(LTrim(Replace(c.Value, "0", " ")), " ", "0")
  Next
End Sub