- Oct 5, 2007
- Office Version
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