Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Insert a new Row

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6

    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8

    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Manet,

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

    Works perfectly.

    Thanks

    Nobby

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •