If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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.. |
#12
|
|||
|
|||
how to insert rows by formula.
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.. |
#13
|
|||
|
|||
how to insert rows by formula.
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 |
#14
|
|||
|
|||
how to insert rows by formula.
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 |
|
Thread Tools | |
Display Modes | |
|
|