Macro to remove leading spaces in Col A

[howard]

I would like to trim leading spaces to the left in Col A on sheets BR1_sales to BR12_Sales

 Sub Remove_Leading_Spaces_CommSheets()
      Dim I As Long, LR As Long
      
    For I = Worksheets("BR1_Sales").Index To Worksheets("BR12_sales").Index
        With Worksheets(I)
         LR = .Cells(Rows.Count, "A").End(xlUp).Row
      For Each r In ActiveSheet.Range("A1:A" & LR)
 If Left(r.Value, 1) = " " Then r.Value = Right(r.Value, Len(r.Value) - 1)
 
 Next I
 Next r
 End With
 
 End Sub

However, when running the macro , I get a compile error: "invalid next control variable reference"


It would be appreciated if someone could kindly amend my code

 

[Peter_SSs]

I haven't checked anything else but for one thing your Next I and Next r are in the wrong order. Swap them.

 

[Peter_SSs]

Actually, there are quite a few things wrong and/or not ideal in that code.

For one thing, if you take more care with your indentations, you would more easily see where things do not match

Sub Remove_Leading_Spaces_CommSheets()
  Dim I As Long, LR As Long
  Dim r As Range
  
  For I = Worksheets("BR1_Sales").Index To Worksheets("BR12_sales").Index
    With Worksheets(I)
      LR = .Cells(Rows.Count, "A").End(xlUp).Row
      For Each r In .Range("A1:A" & LR)
        r.Value = LTrim(r.Value)
      Next r
    End With
  Next I
End Sub

 

[howard]

Thanks Peter

I swopped them around and when I run the macro, I now get error message next without for

Kindly check and advise

 

[howard]

Ingore my last reply. Never saw your second reply

 

[howard]

Thanks Peter. Your code works perfectly

 

[Peter_SSs]

Thanks Peter. Your code works perfectly

You're welcome.

To emphasise my earlier point, here is your original code with coloured lines that should not cross.

 

[howard]

thanks for your input and pointing out where I went wrong