My VBA is messing with date formats - how do i stop it?

[DMO123]

Hi All, i have the below VBA code which trims each cell but i have dates in the workbook and it seems to mess with the date format which then forces you to double click in each cell to correct. Not ideal when there is 10000 rows. Can someone help me on stopping from doing this OR update the code to trim column O only?

Thanks!

Sub CleanSheet1()    For Each Cell In ActiveSheet.UsedRange
        Cell.Value = Trim(Cell)
    Next Cell
End Sub

 

[steve the fish]

You could use the intersect of the used range and column O. Personally instead of the loop id use this:

Sub TrimEvaluate()

Dim x, lr As Long

lr = Range("O" & Rows.Count).End(xlUp).Row
Set x = Range("O1:O" & lr)
x = Evaluate("=IF(ROW(1:" & lr & "),TRIM(" & x.Address & "))")
Range("O1:O" & lr) = x

End Sub

 

[MickG]

Try:-

Dim cell As Range
For Each cell In ActiveSheet.UsedRange
        If Not IsDate(cell.Value) Then
            cell.Value = Trim(cell.Value)
        End If
Next cell

 

[DMO123]

You could use the intersect of the used range and column O. Personally instead of the loop id use this:

Sub TrimEvaluate()

Dim x, lr As Long

lr = Range("O" & Rows.Count).End(xlUp).Row
Set x = Range("O1:O" & lr)
x = Evaluate("=IF(ROW(1:" & lr & "),TRIM(" & x.Address & "))")
Range("O1:O" & lr) = x

End Sub

Works perfectly thank you!

 

[DMO123]

Try:-

Dim cell As Range
For Each cell In ActiveSheet.UsedRange
        If Not IsDate(cell.Value) Then
            cell.Value = Trim(cell.Value)
        End If
Next cell

works perfectly too - thank you!

 

[MickG]

You're welcome

 

[pgc01]

Hi

Only text values can be trimmed.

If you have a big used range it is more efficient, quicker, if you only loop through the text values.

Remark: the first loop is for the constant text values and the second loop is for the text values resulting of formulas. In this case you lose the formulas.

Sub TrimAllText()
Dim r As Range

' trims all text constant values
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
            r.Value = Trim(r.Value)
Next r

' trims all text values result of formulas (you lose the formulas)
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlTextValues)
            r.Value = Trim(r.Value)
Next r

End Sub