Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macros to insert specific rows without altering the formula
I have got a table with data in rows and a formuls in the last row of the
table . I need a macro which will looks up a reference cell which contains the no of rows to be inserted and then inserts the rows accordingly . Also the row containing the formula must include all the data including the newly added rows. Appreciate an urgent feedback. Krishna |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macros to insert specific rows without altering the formula
Hi
When you insert rows between the 1st and last row, the formula will automatically adjust. If you insert a row before the first row, or after the last row, you will have to adjust the formula. Do you always want to insert rows in the same area, or do you want to select an area, and then have the rows inserted there? Assuming the latter, you can use something like the following: Dim iInsert As Integer Sub AddRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 Selection.EntireRow.Insert Next End If End Sub Of course, change E1 to whatever your reference cell is. You can also use an input box to obtain the number of rows to be inserted. That would in my opinion work better, since you then do not have to go to eg Row 1 to insert your criteria, and then back down to row 500 before running the macro? -- Hth Kassie Kasselman Change xxx to hotmail "Mysore" wrote: I have got a table with data in rows and a formuls in the last row of the table . I need a macro which will looks up a reference cell which contains the no of rows to be inserted and then inserts the rows accordingly . Also the row containing the formula must include all the data including the newly added rows. Appreciate an urgent feedback. Krishna |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macros to insert specific rows without altering the formula
Hi Kassie,
Many thanks for your help. It worked. However the new rows did not copy the formula from the above row. Is it possible to make the rows automatically copy the formulas from the previous row. Additionally, I want to delete the extra rows created, is it possible to get a code for that too. Appreciate your help again. Thanks "kassie" wrote: Hi When you insert rows between the 1st and last row, the formula will automatically adjust. If you insert a row before the first row, or after the last row, you will have to adjust the formula. Do you always want to insert rows in the same area, or do you want to select an area, and then have the rows inserted there? Assuming the latter, you can use something like the following: Dim iInsert As Integer Sub AddRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 Selection.EntireRow.Insert Next End If End Sub Of course, change E1 to whatever your reference cell is. You can also use an input box to obtain the number of rows to be inserted. That would in my opinion work better, since you then do not have to go to eg Row 1 to insert your criteria, and then back down to row 500 before running the macro? -- Hth Kassie Kasselman Change xxx to hotmail "Mysore" wrote: I have got a table with data in rows and a formuls in the last row of the table . I need a macro which will looks up a reference cell which contains the no of rows to be inserted and then inserts the rows accordingly . Also the row containing the formula must include all the data including the newly added rows. Appreciate an urgent feedback. Krishna |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macros to insert specific rows without altering the formula
I am sure there are better ways of doing this, but not knowing exactly what
formulae etc you want copied, I changed the code as follows Option Explicit Dim iInsert As Integer Dim iRow As Integer Sub AddRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 iRow = ActiveCell.Row Selection.EntireRow.Insert Range("C" & iRow - 1 & ":D" & iRow - 1).Copy Destination:=Range("C" & Row) iRow = iRow + 1 Next End If iRow = 0 End Sub This assuming that your formulae are in Cols C:D. You can adjust To delete rows, simply change the .insert part to .delete as follows Sub DelRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 iRow = ActiveCell.Row Selection.EntireRow.Delete Next End If End Sub -- Hth Kassie Kasselman Change xxx to hotmail "Mysore" wrote: Hi Kassie, Many thanks for your help. It worked. However the new rows did not copy the formula from the above row. Is it possible to make the rows automatically copy the formulas from the previous row. Additionally, I want to delete the extra rows created, is it possible to get a code for that too. Appreciate your help again. Thanks "kassie" wrote: Hi When you insert rows between the 1st and last row, the formula will automatically adjust. If you insert a row before the first row, or after the last row, you will have to adjust the formula. Do you always want to insert rows in the same area, or do you want to select an area, and then have the rows inserted there? Assuming the latter, you can use something like the following: Dim iInsert As Integer Sub AddRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 Selection.EntireRow.Insert Next End If End Sub Of course, change E1 to whatever your reference cell is. You can also use an input box to obtain the number of rows to be inserted. That would in my opinion work better, since you then do not have to go to eg Row 1 to insert your criteria, and then back down to row 500 before running the macro? -- Hth Kassie Kasselman Change xxx to hotmail "Mysore" wrote: I have got a table with data in rows and a formuls in the last row of the table . I need a macro which will looks up a reference cell which contains the no of rows to be inserted and then inserts the rows accordingly . Also the row containing the formula must include all the data including the newly added rows. Appreciate an urgent feedback. Krishna |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macros to insert specific rows without altering the formula
Something a bit more generic, not these and your previous
replies are subroutines not functions. http://www.mvps.org/dmcritchie/excel/insrtrow.htm The difference between functions and macros Macros And Functions http://www.cpearson.com/excel/differen.htm -- HTH, David McRitchie, Microsoft MVP -- Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "kassie" wrote in message ... I am sure there are better ways of doing this, but not knowing exactly what formulae etc you want copied, I changed the code as follows Option Explicit Dim iInsert As Integer Dim iRow As Integer Sub AddRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 iRow = ActiveCell.Row Selection.EntireRow.Insert Range("C" & iRow - 1 & ":D" & iRow - 1).Copy Destination:=Range("C" & Row) iRow = iRow + 1 Next End If iRow = 0 End Sub This assuming that your formulae are in Cols C:D. You can adjust To delete rows, simply change the .insert part to .delete as follows Sub DelRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 iRow = ActiveCell.Row Selection.EntireRow.Delete Next End If End Sub -- Hth Kassie Kasselman Change xxx to hotmail "Mysore" wrote: Hi Kassie, Many thanks for your help. It worked. However the new rows did not copy the formula from the above row. Is it possible to make the rows automatically copy the formulas from the previous row. Additionally, I want to delete the extra rows created, is it possible to get a code for that too. Appreciate your help again. Thanks "kassie" wrote: Hi When you insert rows between the 1st and last row, the formula will automatically adjust. If you insert a row before the first row, or after the last row, you will have to adjust the formula. Do you always want to insert rows in the same area, or do you want to select an area, and then have the rows inserted there? Assuming the latter, you can use something like the following: Dim iInsert As Integer Sub AddRows() iInsert = Range("E1").Value If iInsert < 1 Then Exit Sub Else For iInsert = iInsert To 1 Step -1 Selection.EntireRow.Insert Next End If End Sub Of course, change E1 to whatever your reference cell is. You can also use an input box to obtain the number of rows to be inserted. That would in my opinion work better, since you then do not have to go to eg Row 1 to insert your criteria, and then back down to row 500 before running the macro? -- Hth Kassie Kasselman Change xxx to hotmail "Mysore" wrote: I have got a table with data in rows and a formuls in the last row of the table . I need a macro which will looks up a reference cell which contains the no of rows to be inserted and then inserts the rows accordingly . Also the row containing the formula must include all the data including the newly added rows. Appreciate an urgent feedback. Krishna |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Move source without altering formula | Excel Discussion (Misc queries) | |||
Trouble using macros to insert rows | Excel Discussion (Misc queries) | |||
Altering VLookup Formula | Excel Worksheet Functions | |||
Formula keeps altering by itself! | Excel Worksheet Functions |