Conditional row insertion

biminy

New Member
Joined
Feb 22, 2002
Messages
31
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
 

biminy

New Member
Joined
Feb 22, 2002
Messages
31
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

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
 

Forum statistics

Threads
1,143,752
Messages
5,720,637
Members
422,295
Latest member
Ryamker3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top