I am looking for a macro that will look for a value in column P ("ADD") and copy the row above and insert copied cells down below the copied row.
This will have to be looped until there are no remaining instances in that column.
I am looking for a macro that will look for a value in column P ("ADD") and copy the row above and insert copied cells down below the copied row.
This will have to be looped until there are no remaining instances in that column.
I have this for Delete of rows based on value:
![]()
Sub DELETEROWS() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'Set the first and last row to loop through Firstrow = 5 Lastrow = 500 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the P column in this example With .Cells(Lrow, "P") If Not IsError(.Value) Then If .Value = "DELETE" Then .EntireRow.Delete 'This will delete each row with the Value "delete" 'in Column P, case sensitive. End If End With Next Lrow End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub
try this
![]()
Sub addrow() Lastrow = Cells(Rows.Count, "P").End(xlUp).Row For j = 5 To Lastrow If Cells(j, "P").Value = "ADD" Then Rows(j).Insert Shift:=xlDown Rows(j - 1).Copy Rows(j) j = j + 1 Lastrow = Lastrow + 1 End If Next End Sub
Thats almost it. It loops through once, but needs to keep looping back through until no "ADD"s
For example: in my file, delete rows 8 and 9. Run the macro - it performs for the first, but as you can see with my formula in the cell, there is still an "add" in the column.
I need to keep looping through until all rows are copied down and the "add" is gone.
eliminate
![]()
j = j + 1 Lastrow = Lastrow + 1
That worked. Thank you for your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks