VBA code to insert 2 rows based on condition

clau818

New Member
Joined
Oct 31, 2016
Messages
32
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,315
Office Version
  1. 365
Platform
  1. Windows
You can use a lot of the same logic/techniques I showed you yesterday:
Code:
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

Board Regular
Joined
Nov 3, 2016
Messages
72
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,277
Messages
5,836,343
Members
430,421
Latest member
Natas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top