Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Insert a row based on a condition is Excel

Posted on 2011-09-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 42

Assisted Solution

dlmille earned 1000 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 1000 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 42

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 42

Expert Comment

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

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

609 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