# Conditional row insertion

#### biminy

##### New Member
I hae a spreadsheet table that is sorted by lets say column A in ascending order. I am looking for a way to insert a row (or 2) with each change in value in column A. I do not know how to code any VBA but am familiar with the VBA editor in a mcro and am hoping to be able to set up a macro where I can paste the necessary code for this function? Unless there is another way to do this that anyone may be aware of, does anyone know the code that can be used to generate this result?

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
For a list beginning in cell B2...

1. Enter, the formula, =(B2<>B1)+A1, into cell A2, and fill down to the last entry in column B.

2. Enter the formula into the 1st empty cell below the last used cell in column A that adds 1 to the cell reference above it (e.g., =A13+1), and fill down until a resultant value of 1 is reached.

3. Sort the list in ascending order on the column A values to get the results on the right in the example below...
Book1
ABCDEFGHIJK
1
21Cat1Cat
31Cat1Cat
41Cat1Cat
52Dog0
62Dog1Dog
72Dog1Dog
83Mouse1Dog
93Mouse0
104Bird1Mouse
114Bird1Mouse
124Bird0
134Bird1Bird
1431Bird
1521Bird
1611Bird
17
Sheet4

This message was edited by Mark W. on 2002-09-18 11:51

works! thanks very much.

Question, when you use the formula =(B2<>B1)+A1, does the (B2<>B1)part imply a formula that says if this statement is true, excel will generate a result value = 1, as if you used an "if" formula
= if(B2<>B1,1,0)+ A1 ?

On 2002-09-18 04:53, biminy wrote:
works! thanks very much.

Question, when you use the formula =(B2<>B1)+A1, does the (B2<>B1)part imply a formula that says if this statement is true, excel will generate a result value = 1, as if you used an "if" formula
= if(B2<>B1,1,0)+ A1 ?

Esentially, yes. In fact B2<>B1 returns a boolean value (TRUE or FALSE), and the subsequent arithmetic operator coerces the boolean value into a numeric one (i.e., TRUE becomes 1 and FALSE becomes 0). Data type coercion is a very useful Excel concept that you'll see used quite frequently by contributors to this bulletin board. Here as some more examples of coercion:

=TRUE+0 is 1
=1&"" is "1"
="1"+0 is 1
="9/18/02"+0 is 37517 (the 1900 date system value for Sept 18, 2002)
="9/2002"+0 is 37500 (the 1900 date system value for Sept 1, 2002)
="20:00"+0 is 0.8333... (the time value for 8:00 PM)
This message was edited by Mark W. on 2002-09-18 06:42

Try this macro:

Sub InsertRow_At_Change()
Dim i As Integer
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty(Selection(i - 1)) Then
With Selection(i).Resize(1, 1)
.EntireRow.Insert
End With
End If
Next
End Sub

Notes:
1. Copy/paste the macro into a standard module and assign the macro to a button from the Forms menu.
2. Sort your data first (important).
3. Select your range before running the macro. If you have a very large contiguous range, select any cell in the range then use "Ctrl *" together (without the apostrophes). The "*"is on your numeric keypad.
4. The macro is currently set to add one empty row on each change in the data. If you want say two empty rows, then change the 7th line of the code:

With Selection(i).Resize(1, 1)

to:

With Selection(i).Resize(2, 1). If you want 3 empty rows change to (3,1) etc.

Regards,

Mike

Mark,

*that* is brilliant

nice one,
Chris

thanks to Mark and EKIM for the help and additional tips

Replies
30
Views
684
Replies
3
Views
126
Replies
17
Views
692
Replies
3
Views
178
Replies
18
Views
455

1,219,941
Messages
6,151,086
Members
451,007
Latest member
gianmatt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back