?
Solved

Insert a row based on a condition is Excel

Posted on 2011-09-23
6
Medium Priority
?
223 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
  • 3
5 Comments
 
LVL 42

Assisted Solution

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

-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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

755 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