CODEWITHSUNDEEP
vbaexcelinsert
Srpic
Srpic

Reputation: 450

VBA - find values in columns and insert blank rows in front of those cells

I want to find cells, which contain an exact value and insert in front of those cells blank rows. I already have code, which will find and insert those rows, but only behind those cells.

The code is here:

Private Sub SearchnInsertRows()

Dim LastRow As Long
Dim rng As Range, C As Range
Dim vR(), n As Long


With Worksheets("INPUT_2") ' <-- here should be the Sheet's name
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' last row in column A
    Set rng = .Range("A1:A" & LastRow) ' set the dynamic range to be searched

    ' loop through all cells in column A and copy below's cell to sheet "Output_2"
    For Each C In rng
        If C.Value = "Workflow" Then
            .Range(Cells(C.Row + 1, 1), Cells(C.Row + 8, 8)).EntireRow.Insert
        End If
    Next C


End With

End Sub

This code will add 8 rows behind all cells, which contain word "Workflow", but I cannot figure it out, how to put them in front of cells "Workflow"

I thought, that when I put - instead of +, it should solve it, but when I change this line this way:

.Range(Cells(C.Row - 1, 1), Cells(C.Row - 8, 8)).EntireRow.Insert

and run it, the excel will stuck and still adding rows.

Could I ask you for an advice, what do I do incorrectly, please?

Many thanks

Upvotes: 0

Views: 3019

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

Instead of an For Each loop use a For i = LastRow to 1 Step -1 loop to loop backwards from last row to first. Inserting or deleting rows has always to be done backwards (from bottom to top) because then it will only affect rows that are already processed otherwise the row-counts of unprocessed rows will change and mess up the loop.

Something like the following should work:

Option Explicit 'Very first line in a module to enforce correct variable declaring.

Private Sub SearchAndInsertRows()
    Dim lRow As Long, iRow As Long

    With Worksheets("INPUT_2") ' <-- here should be the Sheet's name
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' last row in column A

        'loop backwards (bottom to top = Step -1) through all rows
        For iRow = lRow To 1 Step -1
            'check if column A of current row (iRow) is "Workflow"
            If .Cells(iRow, "A").Value = "Workflow" Then
                .Rows(iRow).Resize(RowSize:=8).Insert xlShiftDown
                  'insert 8 rows and move current (iRow) row down (xlShiftDown)
                  'means: insert 8 rows ABOVE current row (iRow)

                '.Rows(iRow + 1).Resize(RowSize:=8).Insert xlShiftDown
                   'alternatively use .Rows(iRow + 1) to insert BELOW current row (iRow)
            End If
        Next iRow
    End With
End Sub

Upvotes: 1

Related Questions