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
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..

You would need a macro, not a formula.
--
Gary''s Student - gsnu200755


"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..

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 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 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

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..