how do I get a new row to add to a column automatically?

[Guest]

I want to know how I can get a new row to add at the end of my data entry w/o
having to manually click "insert" "rows".

In column A, there are entries in rows 1-20. The TOTAL of these rows is
displayed in row 22 of column A. At this point I have 1 more row to enter
data before there are no rows between the data entries and the total. This
will be a column that gets multiple entries, and the TOTAL row will need to
move down to allow for more data.

HOW do I get rows to add automatically when I need them, instead of having
to manually "insert" "rows" every time I need to make an entry in this column?

Thanks in advance for your help!

 

[Chip Pearson]

Try

Rows(Cells(Rows.Count, "A").End(xlUp).Row - 1).Insert


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...

 

[Guest]

I'm sorry, but I don't understand what this means. I can't find this
sequence on any of the menus of the toolbar. A little more clarification,
please?

Thanks.

 

[David McRitchie]

Hi Cathy,
Chip supplied you with a line of macro code and you would have to include it in a macro.

Sub Insert1upfrombottom()
Rows(Cells(Rows.Count, "A").End(xlUp).Row - 1).Insert
End Sub

Instructions to install and use a macro can be found in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

However there is a much better way of doing this, so that you do not
need a blank row before your total row, see
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
and note the part about rewriting your formulas referencing the previous
row so that you use OFFSET..

 

[mht53nyc]

I've tried copying and pasting the insrtow row macro code and am unable
to get excel to recognize it. It does not show up in the macro list
whether I put in a regular workbook or personal.xls.

I also reviewed the macro overview on the website. But no luck.
Security is set to medium and I also tried low. Other macros I recorded
work.

Any suggestions. Thanks in advance.

Thanks

Mike

 

[Peo Sjoblom]

Press Alt + F11, do insert>module, paste it into where the cursor defaults
to
press alt + Q to close the VBE and save the workbook, then press alt + F8
and double click the macro to run it
If you are trying the event macro you need to paste it into the sheet
module, right click sheet tab and select view code and paste it there

--
Regards,

Peo Sjoblom

(No private emails please)

 

[mht53nyc]

Peo:

Tried it just now again. After Alt-F8 the Macro Name Box entries are
blank. (This was in the workbook and not Personal.XLS since I
understand it should not matter).

TIA

Mike

 

[Norman Jones]

Hi Mike,

(This was in the workbook and not Personal.XLS since I understand
it should not matter).

When you call up the Macro Dialog with Alt-F8, you need to select either the
'This Workbook' or the 'All Open Workbooks' option in the 'Macros in'
dropdown.

 

[mht53nyc]

Norman:

Thank you. There are no choices in the dialog box or dropdown. BTW I am
using Excel 2002 with Service Pack 3. I don't know if anyone has tried
this macro with this config. I ahve to assume that I have corrupted
something. Later today, I am going to try differetn macros to see if it
is a global problem or something specific to this macro. I hope I can
gewt it to work since the funcationality it provides is exactly what I
need.

Thanks,

Mike

 

[mht53nyc]

I just tried the macro code that Chip suggested earlier in this thread
and it worked fine. It shows up in Macro box and executes. I was
wondering if someone whio has the same config as me could try
copy/pasting David's code and see if works for them.

Thanks.

Mike

 

[Norman Jones]

Hi Mike,

If Chip's code works for you, so too should David's suggestion: the two are
completely synonymous.

 

[Gord Dibben]

Mike

Is this the Sub you copied from David's site?

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)

It has arguments, you won't see it in the Tools>Macro>Macros dialog and must
be called from another Sub.

Generally the Sub is written to be called from an event which David shows
later on down the page.

The following is an Event Macro, and will insert 3 rows without prompting,
this one is triggered by a Double Click. (see next two topics about
installaton of an Event macro)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True 'Get out of edit mode
Application.Run "personal.xls!InsertRowsAndFillFormulas", 3
End Sub


Gord Dibben Excel MVP

 

[mht53nyc]

Gord:

Thank you!! I will try this later. It was not apparent that I needed to
do this when I looked at the page that contained the code but after
your response I followed David's link on PROPER and I see it is
described in detail.

Thank you again.

Mike

 

[Gord Dibben]

Thanks for the feedback and good luck with the project.


Gord

 

[David McRitchie]

Hi Mike (and Gord),

Actually, I invoke InsertRowsAndFillFormulas
from a **toolbar button** , but I did also include several
Event macros on the page that could be used. I use the
toolbar button for all but a small number of pages.
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Information on toolbar buttons and a picture of toolbar
buttons that you can copy and paste to your toolbar are in
http://www.mvps.org/dmcritchie/excel/toolbars.htm#macros

If you want a subroutine to use from Alt+F8 (macros) you could use
the following, which is now included:

Sub InsertRowsAndFillFormulas_caller()
Call InsertRowsAndFillFormulas
End Sub


"Gord Dibben" <gorddibb ...... wrote