For Next...is there a way to increase the loop

[cmazur71]

In my example, i start with "For i = 1 to a"
During the loop, i am inserting a row, so I want a to increase a by 1
Is there a way to make a increase everytime i insert a row?


a = Worksheets("Clean").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To a
If Worksheets("Clean").Cells(i, 1).Value >= 6000000 And Worksheets("Clean").Cells(i, 1).Value <= 7999999 Then
Worksheets("Clean").Cells(i, 5).Clear 'clears the List Price
Worksheets("Clean").Cells(i, 7).Clear 'clears the Discount
Worksheets("Clean").Cells(i, 9).Clear 'clears the Purchase Price
Worksheets("Clean").Rows(i).Insert shift:=xlDown, CopyOrigin:=xlFormatFromAbove 'inserts a row above
i = i + 1 'next row
a = a + 1 'add a row to the total number of rows in Clean
End If
Next

 

[Trebor76]

Hi cmazur71,

If you are inserting or deleting one row at time like this it best to work bottom to top like so:

Option Explicit
Sub Macro1()

    Const xlFormatFromAbove = 0

    Dim a As Long, i As Long
    
    Application.ScreenUpdating = False
    
    a = Worksheets("Clean").Cells(Rows.Count, 1).End(xlUp).Row

    For i = a To 1 Step -1
        If Worksheets("Clean").Cells(i, 1).Value >= 6000000 And Worksheets("Clean").Cells(i, 1).Value <= 7999999 Then
            Worksheets("Clean").Cells(i, 5).Clear 'clears the List Price
            Worksheets("Clean").Cells(i, 7).Clear 'clears the Discount
            Worksheets("Clean").Cells(i, 9).Clear 'clears the Purchase Price
            Worksheets("Clean").Rows(i).Insert shift:=xlDown, CopyOrigin:=xlFormatFromAbove 'inserts a row above
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,

Robert

 

[diddi]

a
With Worksheets("Clean") - End With
might speed things up a bit more?

 

[cmazur71]

Hi cmazur71,

If you are inserting or deleting one row at time like this it best to work bottom to top like so:

Option Explicit
Sub Macro1()

    Const xlFormatFromAbove = 0

    Dim a As Long, i As Long
   
    Application.ScreenUpdating = False
   
    a = Worksheets("Clean").Cells(Rows.Count, 1).End(xlUp).Row

    For i = a To 1 Step -1
        If Worksheets("Clean").Cells(i, 1).Value >= 6000000 And Worksheets("Clean").Cells(i, 1).Value <= 7999999 Then
            Worksheets("Clean").Cells(i, 5).Clear 'clears the List Price
            Worksheets("Clean").Cells(i, 7).Clear 'clears the Discount
            Worksheets("Clean").Cells(i, 9).Clear 'clears the Purchase Price
            Worksheets("Clean").Rows(i).Insert shift:=xlDown, CopyOrigin:=xlFormatFromAbove 'inserts a row above
        End If
    Next i
   
    Application.ScreenUpdating = True
   
End Sub

Hope that helps,

Robert

Thank you...it worked perfectly

 

[Trebor76]

You're welcome