Preserving Range data Type while trimming trailing spaces.

[cyberwolf]

I found this thread in my search Trim Column in VBA and was able to convert Rick Rothstein's code to loop through all of the columns in a worksheet. Code will be listed below. Trouble is We have columns that have data with leading zero's, and, this code see to convert each column to general if it looks like a number. Is there anyway to preserve the existing format and still trim spaces?

Sub TrimColumn()
Dim Addr As String
Dim lstCol As Long, lstRow As Long
Dim varCol As Variant
lstCol = Cells(1, Columns.Count).End(xlToLeft).Column
lstRow = Cells(Rows.Count, "A").End(xlUp).Row
For varCol = 1 To lstCol
Addr = Range(Cells(2, varCol), Cells(lstRow, varCol)).Address
Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
Next varCol
End Sub

 

[Jeffrey Mahoney]

As far as I can see this code only removes spaces at the beginning and end of a text string. If you have hard coded zeros in a cell, this shouldn't remove those.

 

[cyberwolf]

As far as I can see this code only removes spaces at the beginning and end of a text string. If you have hard coded zeros in a cell, this shouldn't remove those.

It wasn't removing them. The problem was the data is exported from a dataset using a macro. The column in question was actually set to General. But the data was pasted in as text. So when that trim function was run on it. Once the data was trimmed Excel looked at the column as a General data type column and the leading zeros were dropped. Either way I was able to find a work around.