macros to insert specific rows without altering the formula

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[David McRitchie]

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