Conditional row insertion

[biminy]

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?

 

[Mark W.]

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
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	Cat			1	Cat
3	1	Cat			1	Cat
4	1	Cat			1	Cat
5	2	Dog			0
6	2	Dog			1	Dog
7	2	Dog			1	Dog
8	3	Mouse			1	Dog
9	3	Mouse			0
10	4	Bird			1	Mouse
11	4	Bird			1	Mouse
12	4	Bird			0
13	4	Bird			1	Bird
14	3				1	Bird
15	2				1	Bird
16	1				1	Bird
17
Sheet4

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

 

[biminy]

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 ?

 

[Mark W.]

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

 

[Ekim]

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

 

[Chris Davison]

Mark,

*that* is brilliant

nice one,
Chris

 

[biminy]

thanks to Mark and EKIM for the help and additional tips