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

Topic

New Reply

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?

Reply | Quote

Replies

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…

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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

Reply | Quote

For a non macro way of doing this, see Hans’ Post.

Reply | Quote

For a non macro way of doing this, see Hans’ Post.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

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

Reply | 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?

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.

Reply | Quote

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.

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

Reply | Quote