Insert a row based on a condition is Excel

Posted on 2011-09-23
Last Modified: 2012-05-12
I have a data dump as one large file.  I have the data in Excel 2010 but now need to insert an empty row after the City, State, Zip row to facilitate creating 'a mailing label'.

The last row for each address contains USA as the last data element in the row.  I don't know how to create a macro but can learn if you share with me.  

Can you help?
Question by:SQLwatcher
  • 3
LVL 41

Assisted Solution

dlmille earned 250 total points
ID: 36591254
Do you need an empty row at the end of the data dump?  Or, do you need a blank row for every city,state,zip of data (e.g., a blank row after every line of data)?

Can you upload a small extract of your data - just desensitize it so nothing confidential is shared.  That will help assist in creating a macro...

I'll give it a shot, "blind", however.  You can try this code.

In your Excel 2010 file, hit ALT-F11 - that will take you to the VBA editor for Excel.  Then, on the left side, you should see your VBA Project, with some nodes listing down on the left.  E.g., Microsoft Excel Objects, Sheet1, Sheet2, etc., then ThisWorkbook.  Just click your right mouse button in there and select INSERT->Module.  Then copy/paste this code in.

Sub insertRowForEachDataLine()
Dim wkb As Workbook
Dim wks As Worksheet
Dim myCell As Range
Dim lastRow As Long
Dim myCursor As Range

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    'search column A, starting at A2, until no more data
    'assumes data on every row.  When there is no data on a row, we're done
    Set myCursor = wks.Range("A2")
            myCursor.Offset(1, 0).EntireRow.Insert
            Set myCursor = myCursor.Offset(2, 0) 'skip to next data row
    Loop While myCursor.Value <> ""
End Sub

Open in new window

The macro starts in Row 2, looking in Column A of the Active Workbook.  It then inserts rows after every data row.  It moves down the list until it finds now data in a row in column A.

Remember to upload a sample, if this doesn't work for you!


LVL 27

Accepted Solution

Glenn Ray earned 250 total points
ID: 36591285
Assuming that all the data is contained in column A (starting in cell A1) in Excel, the following macro code should insert a new row after each occurrence of "USA" on its own row.

Sub Insert_Rows()
    Dim c As Object
    Dim rng As Range
    Range("A1", Selection.End(xlDown)).Select
    Set rng = Selection
    For Each c In rng
        If c.Value = "USA" Then
            c.Offset(1, 0).EntireRow.Insert shift:=xlShiftDown
        End If
    Next c
End Sub

Open in new window

To create this macro:
Start VBA in Excel (keyboard shortcut: [Alt]+[F11])
Find your Excel file in the Project Explorer window (top left)
Right-click on your file and select Insert>Module from the shortcut menu
Paste the code below in the code window on the right

To run it:
Switch back to Excel
Press [Alt]+[F8] to call up the Macros window (or menu: Developer, Macros)
Select "Insert_Rows" from the box and click the "Run" button.

LVL 41

Expert Comment

ID: 36591294
@GlennLRay - Depending on the users's last worksheet selection, the code would run until it gets to the bottom of the entire workbook - quite a long time.

I also suggest not Selecting anything in the workbook, unless you have to - very rarely the case.

The command :

    Range("A1", Selection.End(xlDown)).Select

Depends on where the user LAST selected in the workbook.  SQLWatcher would need to carefully select the last column (or a data column, anyway) before running the macro.  If SQLWatcher selects on a column that has no data, Select.End(xlDown) would make that range hit the bottom of the worksheet.

I'd suggest a rewrite to:

Sub Insert_Rows()
    Dim c As Range
    Dim rng As Range
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp))

    For Each c In rng
        If c.Value = "USA" Then
            c.Offset(1, 0).EntireRow.Insert shift:=xlShiftDown
        End If
    Next c
End Sub

Open in new window


SQLWatcher would need to subtitute the reference to the column "A" for the column in the dataset that has COUNTRY for it to work, but I like your thinking :)



Author Closing Comment

ID: 36816869
Thank you both for awesome information!.  I was able to get the spreadsheet into 'mailing list' format with minor clean up --inconsistent address entry- so all is good!  Since you both provided valuable information, I split the points; I hope that is OK with you.
LVL 41

Expert Comment

ID: 36817099
Always happy to collaborate.  Glad you got a solution that worked!

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now