Excel: New row at the end with a macro?

[Benoni2]

I want a button that inserts a new row at the bottom when they have finished up with the available rows. So far though the insert button when recording a macro adds it above the selected record. Also, when repeated, it does the action off of the record that you origonally selected which pretty quickly is no longer near the bottom, but somewhere in the middle.

Case:

A1
A2
A3
A4

Select A4, Insert. New A4, Old A4 becomes A5.

Run it again, it starts with A4 again.

Soon:

A1
A2
A3
A4 - New row keeps getting inserted here.
A5
A6
A7

I want it to show up after the last row inserted. Or maybe right above the footer row? Any help? VBA or a macro?

 

[AnotherHiggins]

If I understand what you are trying to do with this, how about a formula?
In A4, type:
[COLOR=blue white]=if(b4="","A4")[/color]
You can fill that way down the sheet and it only becomes visible when someone types something in the B column.

Or perhaps I don't understand what your end goal is?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

What is the reason for the INSERT?

Could you not just add data to the bottom and sort?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Benoni2]

Interesting. anotherhiggins has an interesting thought I might play with. But what I have is an activities sheet. It asks for the same information over and over again on each row, A through G.

Right now I give them about enough rows to fill a printed page. When they get down to the last row that I give them, the next row is the footer row which is colored and not ment for input.

I need for them when they get down to the last row to have the option of inserting an additional row right there below the last record I give them and above the row that I am calling my footer for the sheet. I hope this explains.

 

[Benoni2]

What I would like to do is program a button that inserts a new row (record) after the last row on the form instead of immediately above the row.

 

[SkipVought]

What happens when the "footer" gets pushed down to the next page on the sheet?

If need be, make the footer row SORT to the bottom. Much better than INSERT, which is the bane of inter-row formulas.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Benoni2]

What about something like what I thought anotherhiggins was suggesting, but a little different?

Is there an easy way just to hide rows until required? So I have:

A B C D
Row1
Row2
Row3
Row4
Row5 (A5: Statement: =If B4 is blank, hide this row)
Row6 (A6: Statement: =If B5 is blank, hide this row)

I could make way more than I want to show rows or records down the page, but it wouldn't show any additional record until the record immediately above it starts getting filled in. Is that possible to program?

 

[SkipVought]

use Conditional Formatting. Make the Font Color the same as the interior color

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Benoni2]

I figured it out. Here is the code. Yesterday I think it was anotherhiggins who helped me with naming cells or a range of cells. This allowe me to select my named cell and insert a new record from there always adding it above my footer. the new row keeps my formatting from the above rows which is what I needed it to do.

Sub button_new()
'
' button_new Macro
' Macro recorded 8/19/2005 by Benjamin Shier
'
' Keyboard Shortcut: Ctrl+n
'
Range("footerrow").Select
Selection.Insert Shift:=xlDown

End Sub

This is what I was having the problem with:

Rows("36:36").Select
Selection.Insert Shift:=xlDown
Range("A37").Select

It would keep inserting rows at this location meaning that even after someone filled out records below this, the new record would be inserted above it at this point. Thanks for the help guys.

 

[AnotherHiggins]

I'm not clear on why you want to make a row a 'footer', and why you want to be able to bump that row down indefinitely.

Just to make sure: you are aware of the Header/Footer options in File > Page Setup, right?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

SORT! SORT! SORT!

Think out-of-the-box!

Maybe a hidden column contaning a HIGH VALUE for the FOOTER ROW???

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}