CODEWITHSUNDEEP
vbaexcel
Ben Wong
Ben Wong

Reputation: 701

Dynamically adjust for last row as new rows are inserted

I am doing a loop on a set of data and need to insert new rows based on a certain criteria. However, my loop (based on the last row) is not accounting for these new rows I inserted. In my loop, I added "1" to make the adjustment. Thanks. 

Sub myLoop()

Dim lastRow As Integer: lastRow = Cells(Rows.Count, 1).End(xlUp).Row

lastCol = Cells(1, Columns.Count).End(xlToLeft).Column


For x = 1 To lastRow
  If Cells(x, 1).Value = "APHB" Then
    Rows(x).EntireRow.Copy
  End If

  If Cells(x, 1).Value = "APLB" Then
    Rows(x + 1).EntireRow.Insert

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
  End If
Next x

End Sub

Upvotes: 0

Views: 697

Answers (1)

Rdster
Rdster

Reputation: 1872

You could try something like this...

Sub myLoop()
Dim lastRow As Integer, x as Integer

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
x = 1
Do
  If Cells(x, 1) = "APHB" Then
    Rows(x).EntireRow.Copy
  ElseIf Cells(x, 1)= "APLB" Then
    Rows(x + 1).EntireRow.Insert
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
  End If
  x = x + 1
while x < = lastRow

End Sub

Upvotes: 2

Related Questions