Solved

Insert a row based on a condition is Excel

Posted on 2011-09-23
6
199 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
6 Comments
 
LVL 41

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 41

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 41

Expert Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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 …

708 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

17 Experts available now in Live!

Get 1:1 Help Now