how to insert rows by formula.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
that will help a little, thanks

FSt1 said:
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
Click to expand...
 
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
 
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.
 
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
 
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.
 
Hi Peter,
the macro only insert blank rows up to row 40, no blank line afterthat. how
could I modify the macro.

Thx
 
after rows 40. the number is 9,10,11,12,13........ how to modify the macro to
insert blank rows after those number.
 
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

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
 
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"
 
Back
Top