When I use this code...

Posted by Meredith L on January 09, 2002 2:11 PM

What I am trying to do is insert lines wherever the values in a column differ. I will eventually expand this code. The problem is that my For X number changes as the rows get inserted. So, if initially, the number of rows in the region is 50, and the code winds up inserting 5 rows, the evaluation equation stops at row 50, when it should not continue on to row 55.

The main question is "is there a way to 'reset' the For X number in midstream?

Mere

Sub InsertLines()

For x = 1 To (Sheets("Data Input").Range("c16").CurrentRegion.Rows.Count)
If Cells(x, 3).Value &LT;&GT; Cells(x + 1, 3).Value Then
Cells(x + 1, 3).EntireRow.Insert
x = x + 1
End If
Next x

End Sub

Posted by Jacob on January 09, 2002 2:56 PM

The easy solution just check from x = 1 to (range("A65536").end(xlup).row)*2

HTH
Jacob

Posted by Aufidius on January 09, 2002 3:38 PM

Try this :-

Sub InsertLines()
Dim x As Long, cell As Range
x = Range("A1").CurrentRegion.Rows.Count + 1
Set cell = Range("C1")
While cell.Row &LT;&GT; x
If cell.Value &LT;&GT; cell.Offset(1, 0).Value Then
cell.Offset(1, 0).EntireRow.Insert
x = x + 1
Set cell = cell.Offset(2, 0)
Else: Set cell = cell.Offset(1, 0)
End If
Wend
End Sub

Posted by Bariloche on January 09, 2002 10:19 PM

Meredith,

When adding or deleting rows its best to work from the bottom up. Otherwise you encounter the problem that you have. Try the code below:

Sub InsertLines()
Dim LastRow As Long
Dim i As Long

LastRow = Cells(65536, 3).End(xlUp).Row

For i = LastRow To 1 Step -1
If Cells(i, 3).Value &LT;&GT; Cells(i + 1, 3).Value Then Cells(i + 1, 3).EntireRow.Insert
Next i

End Sub

have fun

Posted by George Eastham on January 10, 2002 1:05 AM

Not sure what you are trying to do.

Your line of code :-
Sheets("Data Input").Range("c16").CurrentRegion.Rows.Count
will not necessarily give a count of all the rows with data in column C, but then you start your loop at C1.

Try Bariloche's suggestion or Aufidius'.
Either of them or both might be what you're looking for.

I think Aufidus' macro has a typo - probably should read :-

Sub InsertLines()
Dim x As Long, cell As Range
x = Range("C1").CurrentRegion.Rows.Count + 1
Set cell = Range("C1")
While cell.Row &LT;&GT; x
If cell.Value &LT;&GT; cell.Offset(1, 0).Value Then
cell.Offset(1, 0).EntireRow.Insert
x = x + 1
Set cell = cell.Offset(2, 0)
Else: Set cell = cell.Offset(1, 0)
End If
Wend
End Sub

Also, please note that the following lines of code may all produce different row numbers - depends what rows you are trying to process :-

Per Bariloche :-
LastRow = Cells(65536, 3).End(xlUp).Row

Per your macro :-
Range("c16").CurrentRegion.Rows.Count

Per Aufidius' macro :-
Range("A1").CurrentRegion.Rows.Count

Per my suggested change to Aufidius' macro :-
Range("C1").CurrentRegion.Rows.Count