Solved

Insert a row based on a condition is Excel

Posted on 2011-09-23
6
208 Views
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?
0
Comment
Question by:SQLwatcher
[X]
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
6 Comments
 
LVL 42

Assisted Solution

by:dlmille
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")
    
    Do
            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!

Enjoy!

Dave
insertRows-r1.xlsm
0
 
LVL 27

Accepted Solution

by:
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.

-Glenn
0
 
LVL 42

Expert Comment

by:dlmille
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 :)

Cheers,

Dave
0
 

Author Closing Comment

by:SQLwatcher
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.
0
 
LVL 42

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

737 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