how to insert rows by formula.

Sorry I have been off line for a while. If you have not gor the answer from
someone else try this

Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row

For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub

Just a different method of choosing the last cell - hope this helps
Peter

"Johnny" wrote:

after rows 40. the number is 9,10,11,12,13........ how to modify the macro to
insert blank rows after those number.


"Johnny" wrote:

Hi Peter,
the macro only insert blank rows up to row 40, no blank line afterthat. how
could I modify the macro.

Thx

"Johnny" wrote:

Hi Peter,

I got it, thank you so much.
your are the best.

ps. I still have anther problem sample below:
This is what I have in my work sheet.

|column column
row | A B
-----|----------------------------------
1 | 9 13
2 | 9 1
3 | 9 15
4 | 9 16
5 | 10 2
6 | 11 1
7 | 11 2
8 | 12 1
9 | 13 5


This is what I want to after formula.
the formula only replaced rows 6-8( column A-B).

|column column
rows| A B
-----|----------------------------------
1 | 9 13
2 | 9 1
3 | 9 15
4 | 9 16
5 | 10 2
6 | text N101
7 | text N102
8 | text N103
9 | 13 5


regards
Johnny.

"Billy Liddel" wrote:

Johnny

the macro needs to be copied into a VB Module. Hold Alt and press the F11
function key. Choose Insert, Module copy the code into the module and then
press Alt + Q to return to the workbook.

Activate the sheet with data and choose Tools Macro (or Alt + F8) select the
macro and click run.

the macro assumes that the data starts at A1 and that there are no blank
rows. If this is not the case it will have to be rewritten.

Peter

"Johnny" wrote:

Hi peter,

I had copy your macro and run it, but nothing hapen.
(what I did is copy your macro and paste and run)
am I do right?

regards
Johnny.

"Billy Liddel" wrote:

Hi Johnny
try this macro

Sub InsertRow()
Dim c, d, nr As Long
nr = Range("A1").CurrentRegion.Rows.Count

For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub


Regards
Peter

"Johnny" wrote:

that will help a little, thanks

"FSt1" wrote:

hi
formulas return values. they can not perform actions like insert row.
there is a keyboards shortcut for it.
ctrl+ plus sign - insert a cell
crtl+ minus sign - removes a cell
high light row ctrl+ plus sign - adds a row
high light row ctrl+ minus sign - removes a row

regards
FSt1

"Johnny" wrote:

Hi
anyone know how to insert a blank rows by use formula at cell A between 1&2,
2&3, 3&4.

cell
A B
--------------------------------
1 7
1 8
2 1
2 2
2 6
2 7
3 7
3 15
3 16
3 3
4 3
4 4


Thanks..

Johnny

I just realised that if you are adding rows or already have run the macro to
get partial results then I should have rewritten the code. Ig note the one
above and use the following:

Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row

For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsEmpty(d) Or IsEmpty(c) Then
' do nothing
ElseIf IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub

Regards
Peter

"Billy Liddel" wrote:

Sorry I have been off line for a while. If you have not gor the answer from
someone else try this

Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row

For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub

Just a different method of choosing the last cell - hope this helps
Peter

"Johnny" wrote:

after rows 40. the number is 9,10,11,12,13........ how to modify the macro to
insert blank rows after those number.


"Johnny" wrote:

Hi Peter,
the macro only insert blank rows up to row 40, no blank line afterthat. how
could I modify the macro.

Thx

"Johnny" wrote:

Hi Peter,

I got it, thank you so much.
your are the best.

ps. I still have anther problem sample below:
This is what I have in my work sheet.

|column column
row | A B
-----|----------------------------------
1 | 9 13
2 | 9 1
3 | 9 15
4 | 9 16
5 | 10 2
6 | 11 1
7 | 11 2
8 | 12 1
9 | 13 5


This is what I want to after formula.
the formula only replaced rows 6-8( column A-B).

|column column
rows| A B
-----|----------------------------------
1 | 9 13
2 | 9 1
3 | 9 15
4 | 9 16
5 | 10 2
6 | text N101
7 | text N102
8 | text N103
9 | 13 5


regards
Johnny.

"Billy Liddel" wrote:

Johnny

the macro needs to be copied into a VB Module. Hold Alt and press the F11
function key. Choose Insert, Module copy the code into the module and then
press Alt + Q to return to the workbook.

Activate the sheet with data and choose Tools Macro (or Alt + F8) select the
macro and click run.

the macro assumes that the data starts at A1 and that there are no blank
rows. If this is not the case it will have to be rewritten.

Peter

"Johnny" wrote:

Hi peter,

I had copy your macro and run it, but nothing hapen.
(what I did is copy your macro and paste and run)
am I do right?

regards
Johnny.

"Billy Liddel" wrote:

Hi Johnny
try this macro

Sub InsertRow()
Dim c, d, nr As Long
nr = Range("A1").CurrentRegion.Rows.Count

For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub


Regards
Peter

"Johnny" wrote:

that will help a little, thanks

"FSt1" wrote:

hi
formulas return values. they can not perform actions like insert row.
there is a keyboards shortcut for it.
ctrl+ plus sign - insert a cell
crtl+ minus sign - removes a cell
high light row ctrl+ plus sign - adds a row
high light row ctrl+ minus sign - removes a row

regards
FSt1

"Johnny" wrote:

Hi
anyone know how to insert a blank rows by use formula at cell A between 1&2,
2&3, 3&4.

cell
A B
--------------------------------
1 7
1 8
2 1
2 2
2 6
2 7
3 7
3 15
3 16
3 3
4 3
4 4


Thanks..

Hi Billy,
I was thinking along those lines of extending formulas down
a column as well, but the solution wanted turned out to be a
a formula to effect a transpose as Ron's answer was what the
poster wanted.


Anyway in your suggestion you do not want to hardcode 65536
for rows or for that matter 256 for columns, because sooner or
later it was going to get changed and Excel 2007 has changed that.

You might take a look at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
http://www.mvps.org/dmcritchie/excel/offset.htm
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Billy Liddel"
I just realised that if you are adding rows or already have run the macro to
get partial results then I should have rewritten the code. Ig note the one
above and use the following:

Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row

David

Yes I did know about 2007 but thanks for the pointer - it was excelent.

regards
Peter

"David McRitchie" wrote:

Hi Billy,
I was thinking along those lines of extending formulas down
a column as well, but the solution wanted turned out to be a
a formula to effect a transpose as Ron's answer was what the
poster wanted.


Anyway in your suggestion you do not want to hardcode 65536
for rows or for that matter 256 for columns, because sooner or
later it was going to get changed and Excel 2007 has changed that.

You might take a look at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
http://www.mvps.org/dmcritchie/excel/offset.htm
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Billy Liddel"
I just realised that if you are adding rows or already have run the macro to
get partial results then I should have rewritten the code. Ig note the one
above and use the following:

Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row