Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert balnk row on change in Social Security Numb (Excel XP)

    I have a mullti-column, multi-row, spreadsheet that starts off in Column "A" with the Social Security number. I would like to insert a blank row between the first social security number and the second social security number and between the second and third social security numbers and so on through all the social security numbers. Is there some function I missed that will do that?

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert balnk row on change in Social Security Numb (Excel XP)

    If you are after blank rows for sub total reasons, there is a sub-total command in the data menu that you can use. If it is for other reasons, only a macro can perform that action.
    See attached file for a working demo of such a macro...
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert balnk row on change in Social Security Numb (Excel XP)

    Do you want to do this manually? If so, you can right-click the row number (to the left of column A) then click "Insert" on the popup context menu. This will insert a blank row above the row you clicked on. If you want something automated, you'd need a VBA macro. Post back if you need the latter.

    Alan

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert balnk row on change in Social Security Numb (Excel XP)

    For a non macro way of doing this, see <!post=Hans' Post,393540>Hans' Post<!/post>.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert balnk row on change in Social Security

    Here's a non-macro solution that will insert a blank row after each group of identical SSN's.

    Easier to show than explain.

    Ken

  6. #6
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert balnk row on change in Social Security Numb (Excel XP)

    Thanks to all. Both methods work great. The VBA was fast. SAVED ME TONS OF TIME. Thanks again

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I hate to post to such an old thread...but I need to accomplish the same thing and the links referenced in this post are broken--plus I believe a file attachment existed at one time too & its missing.

    My column "A" is a numeric sequence [it starts at 1] and I need to insert 2 blank rows when the numeric value changes. The numeric values are already sequentially in order [1, 2, 3, 4. etc]. So for clarity, when the value changes column A for 1 to 2...I need to insert 2 blank rows after the last 1 value in column A. My data starts in row 1 [no header row] and extends to row 2165. THANKS.

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The following macro will insert two blank rows on every change in data in the A column:
    Code:
    Sub insert2()
    Dim i As Long, lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = lRow To 2 Step -1
    If Cells(i, 1) <> Cells(i - 1, 1) Then
    	Range(Cells(i, 1), Cells(i + 1, 1)).EntireRow.Insert
    End If
    Next
    End Sub

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='773279' date='01-May-2009 11:38']The following macro will insert two blank rows on every change in data in the A column:
    Code:
    Sub insert2()
    Dim i As Long, lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = lRow To 2 Step -1
    If Cells(i, 1) <> Cells(i - 1, 1) Then
    	Range(Cells(i, 1), Cells(i + 1, 1)).EntireRow.Insert
    End If
    Next
    End Sub
    [/quote]

    Mike,
    Thanks for the code...works like a charm.

    I want to understand your logic..it appears that cell pointer goes to the very bottom of the worksheet and moves to the last row with actual data, correct?

    You then work "backwards or move up the column" to calculate the comparison, yes?

    Finally, what part of the code inserts the 2 rows? I see the row insert command but wouldn't this only insert one row? I was able through trial and error to get a formula approach to work by using an extra column--but I could never figure out how to insert 2 rows versus 1.

    Thanks for you patience.
    JimC

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mike,
    Thanks for the code...works like a charm.

    I want to understand your logic..it appears that cell pointer goes to the very bottom of the worksheet and moves to the last row with actual data, correct?

    You then work "backwards or move up the column" to calculate the comparison, yes?
    Yes the macro starts at the bottoms and works its way up. The reason for this is because of the rows being inserted. When you start at the bottom, the inserted rows are inserted under the row being investigated. Since these rows are under the current row, or the row represented by the variable "i", they are not evaluated in the next loop. If you go start at the top row and then insert rows, you have to account for the inserted rows in the loop.

    Finally, what part of the code inserts the 2 rows? I see the row insert command but wouldn't this only insert one row? I was able through trial and error to get a formula approach to work by using an extra column--but I could never figure out how to insert 2 rows versus 1.
    This line inserts the two rows.
    Code:
    	Range(Cells(i, 1), Cells(i + 1, 1)).EntireRow.Insert
    It is the same as selecting the two rows and right clicking and choosing the insert command. For example, you've reached the first change in numbers (the change from 49 to 50 for example). You would highlight the topmost row with the number 50 and the row below and then right click and choose insert. This will shift the everything down two rows.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='773302' date='01-May-2009 14:09']Yes the macro starts at the bottoms and works its way up. The reason for this is because of the rows being inserted. When you start at the bottom, the inserted rows are inserted under the row being investigated. Since these rows are under the current row, or the row represented by the variable "i", they are not evaluated in the next loop. If you go start at the top row and then insert rows, you have to account for the inserted rows in the loop.



    This line inserts the two rows.
    Code:
    	Range(Cells(i, 1), Cells(i + 1, 1)).EntireRow.Insert
    It is the same as selecting the two rows and right clicking and choosing the insert command. For example, you've reached the first change in numbers (the change from 49 to 50 for example). You would highlight the topmost row with the number 50 and the row below and then right click and choose insert. This will shift the everything down two rows.[/quote]

    Mike,
    Thanks for the explaination....appreciated. Now I understand why you started from the bottom. Most of the time I have a hard time with the VBA syntax and how to "loop" it, etc. This time my logic was flawed too....I guess I know why I couldn't get it to work. JimC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •