Macro to Trim all Columns that contain data in a Spreadsheet - Excel 2010

[Johnny Thunder]

Hello,


I have a sprreadsheet that is a direct download from SQL and it always places a blank character in front of some data in random columns. Is there a Macro that will look into all columns with Data and trim all cells so there is no spaces in front of data?

Hoping this is a easy one! Thanks guys!

 

[Mister H]

Hi Johnny:

ON A COPY OF YOUR SPREADSHEET try this:

Sub Remove_Unwated_Spaces_From_Imported_Data()
' [URL]http://www.theexceladdict.com/nlarchive/120307tdg.htm#tip2[/URL]
    Cells.Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

I got this tip from Frances Hayes Excel Tips Newsletter. I just followed his steps and recorded the macro. If worse comes to worse maybe just follow his instructions manually. Here is a link to that:

http://www.theexceladdict.com/nlarchive/120307tdg.htm#tip2

Good Luck,
Mark

 

[Johnny Thunder]

The Macro worked out great! Thanks!