asked on

Insert a row based on a condition is Excel

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?

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

@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

Kathryn S

ASKER

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.

dlmille

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