speed up macro remove Leading Spaces

[howard]

I have the following code below to remove leading spaces

the macro does what it is supposed to do, but it a bit slow (there just under 1000 rows of data)

It would be appreciated if someone could kindly amend my code so as to speed up the process

 Sub Remove_Leading_Spaces_CommSheets()
      Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
      Dim i As Long, LR As Long, R As Range
      
      
    
        With Sheets("Data Import")
      LR = .Cells(Rows.Count, "A").End(xlUp).Row
      For Each R In .Range("A1:E" & LR)
      On Error Resume Next
      
        R.Value = LTrim(R.Value)
      Next R
    End With
  
   Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 End Sub

 

[Radoslaw Poprawski]

There is better way - power query, load it trim it load to a sheet and just refresh.

 

[howard]

Thanks for letting me know

 

[Fluff]

Do you just want to remove leading spaces & ignore any other extra spaces?

 

[howard]

Hi Fluff

I only want to remove any leading spaces

 

[Fluff]

Ok, how about

Sub howard2()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   With Sheets("Data Import")
      With .Range("A1:E" & .Range("A" & Rows.Count).End(xlUp).Row)
         Ary = .Value2
         For r = 1 To UBound(Ary)
            For c = 1 To UBound(Ary, 2)
               Ary(r, c) = LTrim(Ary(r, c))
            Next c
         Next r
         .Value = Ary
      End With
   End With
End Sub

 

[RoryA]

As you should be well aware by now:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: speedup Code to remove leading spaces [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[howard]

My Apologies Rory. I forgot complete to advise of cross post

 

[howard]

Thanks Fluff


Works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.