Macro to Insert a row in Excel

[grisold]

I'm trying to create an ActiveX control button to insert a row at the bottom of my Excel spreadsheet. The thing is though, I want it so that regardless of which cell you're on, it will add a line to the bottom of the list. Therefore, the first time the button is clicked, it might Insert at row 15 and the next time row 16 and so on. I've got this code to start with,

Rows("15:15").Select
Selection.Insert Shift:=xlDown

but need it to move down one row each time it's used. Please advise. Thanks!

 

[SkipVought]

Hi,

If the row you want to insert is at the BOTTOM of your table, why insert at all?

Just reference the next unused row...

dim lNextRow as long

lNextRow = objSheet.Cells(objSheet.Cells.Rows.Count, "A").End(xlUp) + 1

Skip,

_[red][/red]

 

[grisold]

I want to use the Insert function so that my formulas and formatting (including a drop-down menu) are carried down.

 

[PHV]

You may try this:
ActiveSheet.Cells(ActiveSheet.Cells.Rows.Count, "A").End(xlUp).EntireRow.Insert Shift:=xlDown

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886