We help IT Professionals succeed at work.

How to insert a table row BELOW selected row using excel macro

itkadmin
itkadmin asked
on
I'm trying to add and delete rows from a table using macros assigned to buttons
The following works to insert a row above the selected rowm but I want it to insert a row below selected. How do I do this?
 
'create a new table row
Sub Insert_Row_invoice()
' Add a row to the invoice table
    ActiveSheet.Unprotect
    Rows("10:10").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A10").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=False, AllowFiltering:=False
End Sub

Open in new window

Comment
Watch Question

Rob HensonFinance Analyst

Commented:
Rather than below row 10 can you go for above row 11?

Select row 11 before inserting the row. You may then have to change how the formatting is done by selecting the row that you need to replicate the format from and copy and past fromats from it.

Thanks
Rob H

Author

Commented:
that works if it's only one row.
I can insert a row above 11, but makes the new row 11. Then if I add another row, it will be above my new row 11.
I'm trying to set this up for someone who doesn't understand software much at all. I'm working on a "click here, fill this in" approach.
To me, it makes no difference if the rows are inserted above or below. Has no effect on row values either way. The problem is people are used to working top down because we read that way.

If needed I can say, "Sorry, but that's just how excel works", but if I can, I'l like to keep everything as familiar and as easy as possible for the client.
Finance Analyst
Commented:
If you insert rows above or below it will push existing rows down thereby spoiling the top down reading approach. If you want to keep the top down approach the new entry has to go at the bottom of existing which would suggest you don't need a new row but need to identify the last row. There are ways of doing this in code as well.

Thanks
Rob H
Most Valuable Expert 2011
Awarded 2010

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.