Inserting rows in Excel

I was wondering if anyone knows how to tell excel to look at a column and insert a row in between each different value

[Juan Pablo González]

Can you be a little more specific in what you want/expect ? along with some source data ?

[Paul B]

If I understand right, this Marco Written by Barrie Davidson will do what you want
Sub Insert_row()

' Written by Barrie Davidson

Dim Number_of_rows As Long

Dim Rowinsert As Integer

Number_of_rows = Range("A65536").End(xlUp).Row

Ans = InputBox("How many rows do you want to insert ?", "Rows", 1)

If IsNumeric(Ans) Then

Rowinsert = CInt(Ans)

Else

MsgBox "Invalid number entered", vbCritical, "Error"

Exit Sub

End If

Range("A2").Select

Do Until Selection.Row = Number_of_rows + 1

If Selection.Value <> Selection.Offset(-1, 0).Value Then

Selection.EntireRow.Resize(Rowinsert).Insert

Number_of_rows = Number_of_rows + Rowinsert

Selection.Offset(Rowinsert + 1, 0).Select

Else

Selection.Offset(1, 0).Select

End If

Loop

End Sub

Thanks, Paul B it worked great and will save me a lot of time. Again, THANKS!!!!!

[Q45]

That is great. How would I tweak this to only insert one row at a time beneath a highlighted cell?

[Tikas A. Planck]

Select a cell.
Turn on the macro recorder.
Goto Inset>Rows.
Turn off the macro recorder.

You now have your macro.

[msohail]

Hi,
Further to the solution given below, I have pasted the VDB code but not getting the desired results. I want to insert 2 blank after each row with data in it. e.g

aa
a
a

should look like:
aa


a


a

after the auto insert of rows. Can anyone help pls? :o

[Juan Pablo González]

Try using something like this, and then sorting by column B

******** ******************** ************************************************************************>

Microsoft Excel - Libro2 ___Running: xl2000 : OS = Windows ME
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D
1	aa	1	*	*
2	a	2	*	*
3	a	3	*	*
4	*	1	*	*
5	*	2	*	*
6	*	3	*	*
7	*	1	*	*
8	*	2	*	*
9	*	3	*	*
Hoja1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[msohail]

ThanQ Juan, simple but effective, it works.