Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
sassecathy
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Chip Pearson
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

Try

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


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



"sassecathy" wrote in
message
...
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!



  #3   Report Post  
Posted to microsoft.public.excel.newusers
sassecathy
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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.


"Chip Pearson" wrote:

Try

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


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



"sassecathy" wrote in
message
...
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!




  #4   Report Post  
Posted to microsoft.public.excel.newusers
David McRitchie
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"sassecathy" wrote in message ...
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.


"Chip Pearson" wrote:

Try

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


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



"sassecathy" wrote in
message
...
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!






  #5   Report Post  
Posted to microsoft.public.excel.newusers
mht53nyc
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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



  #6   Report Post  
Posted to microsoft.public.excel.newusers
Peo Sjoblom
 
Posts: n/a
Default how do I get a new row to add to a column automatically?


Press Alt + F11, do insertmodule, 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" wrote in message
oups.com...
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


  #7   Report Post  
Posted to microsoft.public.excel.newusers
mht53nyc
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
Norman Jones
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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.


---
Regards,
Norman



"mht53nyc" wrote in message
ups.com...
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



  #9   Report Post  
Posted to microsoft.public.excel.newusers
mht53nyc
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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

  #10   Report Post  
Posted to microsoft.public.excel.newusers
mht53nyc
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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



  #11   Report Post  
Posted to microsoft.public.excel.newusers
Norman Jones
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

Hi Mike,

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


---
Regards,
Norman


"mht53nyc" wrote in message
oups.com...
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



  #12   Report Post  
Posted to microsoft.public.excel.newusers
Gord Dibben
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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 ToolsMacroMacros 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

On 13 Dec 2005 07:34:22 -0800, "mht53nyc" wrote:

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

  #13   Report Post  
Posted to microsoft.public.excel.newusers
mht53nyc
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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

  #14   Report Post  
Posted to microsoft.public.excel.newusers
Gord Dibben
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

Thanks for the feedback and good luck with the project.


Gord

On 13 Dec 2005 11:21:37 -0800, "mht53nyc" wrote:

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

  #15   Report Post  
Posted to microsoft.public.excel.newusers
David McRitchie
 
Posts: n/a
Default how do I get a new row to add to a column automatically?

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...ars.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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gord Dibben" <gorddibb ...... wrote
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 ToolsMacroMacros dialog and must
be called from another Sub.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to highlight current row & column automatically by a color asif4u Excel Discussion (Misc queries) 8 June 12th 07 04:42 PM
IF/AND/OR/DATEIF Issue...sorry...long post... EDSTAFF Excel Worksheet Functions 1 November 10th 05 01:28 AM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
How can I automatically remove duplicate data within a column? MECG Excel Discussion (Misc queries) 1 June 1st 05 12:22 AM
how to highlight current row & column automatically by a color iffi Excel Discussion (Misc queries) 1 February 6th 05 07:30 PM


All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"