Insert a new Row

[Nobby]

The following code was posted by AJ on 5/3, to insert a new row at the bottom of a table, including all of the formula etc.

Sub test()

Lastrow = Range("A65536").End(xlUp).Row
Rows(Lastrow & ":" & Lastrow).Select
Selection.Copy
Rows(Lastrow + 1 & ":" & Lastrow + 1).Select

ActiveSheet.Paste

End Sub

And it does what it was intended to do. However, I would like to amend the code to actually insert a new row and copy the formula and formatting down into the new row. A new row has to be inserted to maintain a gap with data that is held below the table. The above code seems to just transfer the formula and formatting to the next row down rather than insert an actual new row.

I think the additional code I need is something like:

Insert Row Shift:=xlShiftDown

but I don't know if thats right and, where do I put it?

Any help gratefully received.

Nobby

[AJ]

On 2002-04-23 08:46, Nobby wrote:
The following code was posted by AJ on 5/3, to insert a new row at the bottom of a table, including all of the formula etc.

Sub test()

Lastrow = Range("A65536").End(xlUp).Row
Rows(Lastrow & ":" & Lastrow).Select
Selection.Copy
Rows(Lastrow + 1 & ":" & Lastrow + 1).Select

ActiveSheet.Paste

End Sub

And it does what it was intended to do. However, I would like to amend the code to actually insert a new row and copy the formula and formatting down into the new row. A new row has to be inserted to maintain a gap with data that is held below the table. The above code seems to just transfer the formula and formatting to the next row down rather than insert an actual new row.

I think the additional code I need is something like:

Insert Row Shift:=xlShiftDown

but I don't know if thats right and, where do I put it?

Any help gratefully received.

Nobby

Hi Nobby,

The code you probably want is something like

Rows(VariableHoldingRowNumberGoesHere & ":" & VariableHoldingRowNumberGoesHere).Insert Shift:=xlDown

However, I'm a little confused.
The macro above copied the contents of the last row in the sheet to the row below which you said was cool, but, you go on to say that there is data below the table. Does this mean that it's not actually the last row on the sheet you want to copy but rather the last row in a certain range?
Reply with a bit more info and I'll see what I can do!

Rgds
AJ

[Nobby]

Hi AJ,

Yes, you're right, I want to insert a new row at the end of a current range and carry across to the new row all formulae and formatting from the previous row but not the data contents.

The range uses data validation and I have sited my lists below the table range so I need to insert new rows to stop the lists being overrun.

I have kept Col A below the table range clear so the macro test to find the end of the range by checking up from the bottom will work.

Thanks for the help.

Nobby

[Tom Schreiner]

Hi Nobby...
I'm not clear where exactly you will be inserting and copying...
Maybe this will get you on your way?
Tom

Sub test()
Dim LastRow
LastRow = Range("A65536").End(xlUp).Row
Rows(LastRow & ":" & LastRow).Select
Rows(LastRow + 1 & ":" & LastRow + 1).Insert
Rows(LastRow + 1 & ":" & LastRow + 1).FillDown
End Sub

[Nobby]

Thanks for that Tom - nearly there!

This copies the formula, the formatting and the data contents of the row above. However, I only want to copy the formula and formatting - not the data contents of the row above ie I want to see a fully formatted empty row.

Your code inserts the row in the right place - I just need to find a way of keeping the new row blank, apart from formula and formatting.

Cheers

Nobby

[manet]

Dim LastRow As Range
Set LastRow = [A65536].End(xlUp).EntireRow
With LastRow
.Offset(1, 0).Insert
.Copy .Offset(1, 0)
On Error Resume Next
.Offset(1, 0).SpecialCells(xlCellTypeConstants, 23).ClearContents
On Error GoTo 0
End With

[Nobby]

Thanks for that Manet. a couple of problems though - one my ignorance, the other technical.

Firstly, your procedure doesn't have a sub name, so I cant run it from the macro toolbar. Was is the proper way of naming this routine?

Secondly, when I run it from VB area, I get a warning, where "Set" is highlighted with the message "Invalid outside procedure".

Any ideas?

Cheers.

Nobby

[manet]

On 2002-04-24 06:04, Nobby wrote:
Thanks for that Manet. a couple of problems though - one my ignorance, the other technical.

Firstly, your procedure doesn't have a sub name, so I cant run it from the macro toolbar. Was is the proper way of naming this routine?

Secondly, when I run it from VB area, I get a warning, where "Set" is highlighted with the message "Invalid outside procedure".

Any ideas?

Cheers.

Nobby

Of course you have to name the macro with a name of your choice :-
Sub Whatever()
'The code here
End sub

How did you manage to run it from the VBE without giving it a name?
Can think of no reason why you should get an error on the line that starts with Set.
Did you copy and paste the code into your module?

[Nobby]

Manet,

My mistake - I pasted it over a old piece of code and left a rogue bit behind.

Works perfectly.

Thanks

Nobby