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

#### cmazur71

##### Board Regular
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Trebor76

##### Well-known Member
Hi cmazur71,

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

VBA Code:
``````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

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

#### cmazur71

##### Board Regular
Hi cmazur71,

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

VBA Code:
``````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

You're welcome

Replies
12
Views
205
Replies
7
Views
168
Replies
2
Views
83
Replies
1
Views
74
Replies
1
Views
163

1,127,970
Messages
5,627,923
Members
416,282
Latest member
fchagas97

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back