VBA code to insert 2 rows based on condition

[clau818]

Hi all, I need some help writing a code to insert two rows above any row that has the text string "BIG" in column A. I would like for the code to run only in my selection area rather than throughout the entire sheet.

For example;

Column A
IT1
BY
NYC
BIG
TC
BIG

Should look like:

Column A
IT1
BY
NYC


BIG
TC


BIG


Thanks in advance for your help!

 

[Joe4]

You can use a lot of the same logic/techniques I showed you yesterday:

Sub MyInsertRows()

    Dim firstRow As Long
    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find find and last row of selected range
    firstRow = Selection.Rows(1).Row
    lastRow = Selection.Rows.Count + firstRow - 1
    
'   Loop through all selected rows backwards
    For myRow = lastRow To firstRow Step -1
        If Cells(myRow, "A") = "BIG" Then
            Rows(myRow & ":" & myRow + 1).Insert
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[dssJones]

Sub bigInsert()
    Dim rowCount As Long
    Dim rowNbr, topRow As Long
    Dim myArea As Range
    Set myArea = Selection
    Dim xCell As Range
    Dim colNbr As Integer
'   ---------------------------------------------------
'   Macro will only accept one column of rows at a time
'   ---------------------------------------------------
    If myArea.Columns.Count = 1 Then
        colNbr = myArea.Column
        rowCount = myArea.Rows.Count
        topRow = myArea.Cells(1, 1).Row
        rowNbr = myArea.Cells(rowCount, 1).Row
        Do While rowNbr >= topRow
            If Cells(rowNbr, colNbr) = "BIG" Then
                Cells(rowNbr, colNbr).Insert
                Cells(rowNbr, colNbr).Insert
            End If
            rowNbr = rowNbr - 1
        Loop
    End If
End Sub