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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,650
Members
414,083
Latest member
Mrsash

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