Adding rows above rows that contain "NO" in a specific column

ObiWanBaloney

New Member
Joined
May 10, 2012
Messages
23
Hi All,

Here's an example of what I'm trying to do. Here's 3 columns of data:

Yes No Maybe
Yes Yes Maybe
Yes No Maybe
No Maybe Yes

...and so on for ~15000 rows. What I would like to do here is to use VBA to add a blank row above all rows that have "No" in column 2 and only column 2. The end result would look kind of like this:

(new row)
Yes No Maybe
Yes Yes Maybe
(new row)
Yes No Maybe
No Maybe Yes




Thanks!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Beyond_avarice

Board Regular
Joined
Nov 13, 2012
Messages
195
Office Version
  1. 2007
Platform
  1. Windows
Code:
Sub addRow()

Dim lRow As Long
Application.ScreenUpdating = False

lRow = Cells(Rows.Count, 2).End(xlUp).Row

    For i = lRow To 1 Step -1
        If Range("B" & i).Offset(1).Value = "No" Then
            Rows(i + 1).Insert
        End If
    Next i
    
Application.ScreenUpdating = True
End Sub
 

ObiWanBaloney

New Member
Joined
May 10, 2012
Messages
23
Code:
Sub addRow()

Dim lRow As Long
Application.ScreenUpdating = False

lRow = Cells(Rows.Count, 2).End(xlUp).Row

    For i = lRow To 1 Step -1
        If Range("B" & i).Offset(1).Value = "No" Then
            Rows(i + 1).Insert
        End If
    Next i
    
Application.ScreenUpdating = True
End Sub


Works excellently, thank you! =D
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top