Modify macro so it only performs TRIM if leading space is present

I have a macro that trims leading spaces from every cell in a worksheet. In large spreadsheets, it is slow because most cells don't have leading spaces and trimming them is wasted time. How would I modify the macro so that it firsts tests to see if a leading space is present in a cell, and only trimming those where one is? In concept it's simple, eg. read the first character in a cell, iff it is a space, then replace the cell value with the trimmed cell value. Just don't know how to write it in code. Here is my existing macro:

Sub RemoveLeadingSpace()
For Each cel In ActiveSheet.UsedRange
cel.Value = Trim(cel.Value)
Next cel
End Sub

I tried this. It did what is was supposed to (delayed the screen update to the end), but didn't noticeably speed things up. I'd still like to try a modification along the line I suggested originally.

Sub RemoveLeadingSpace()
With Application
        .ScreenUpdating = False
    End With

For Each cel In ActiveSheet.UsedRange
cel.Value = Trim(cel.Value)
Next cel

With Application
        .ScreenUpdating = True
    End With
End Sub

Thanks, Greg, just spotted your reply. Sounds like what I'm looking for. Will try it and report.

Worked like a charm, Greg. Thanks again. I will mark the thread Solved.

Thanks, Alan. These spreadsheets have no forumulas - they are all straight text (they are for genealogy data, not calculation). But I'll try the screen updating suggestion.

Hi there,

See if the following code does what you need:

Sub RemoveLeadingSpace()

    Dim rCell As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

        For Each rCell In ActiveSheet.UsedRange.Cells

            If Left$(rCell.Value, 1) = " " Then
                rCell.Value = Trim(rCell.Value)
            End If

        Next rCell

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Many thanks for your very prompt feedback.

You're welcome - glad I was able to help.

Regards,

Greg M