Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to highlight current row & column automatically by a color | Excel Discussion (Misc queries) | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
How can I automatically remove duplicate data within a column? | Excel Discussion (Misc queries) | |||
how to highlight current row & column automatically by a color | Excel Discussion (Misc queries) |