Inserting Rows with Macro and expanding Named Range

Graham T

New Member
Joined
Jul 19, 2007
Messages
13
Hi

I'm more of an Access user than Excel but have been asked to produce a worksheet that does certain tasks and have become a little stuck with some of the functionality.

There are two things that I can not fathom out how to perform, and wondered if anyone could point me in the right direction.

1. I am using code to insert a new row and copy formula (code courtesy of http://www.mvps.org/dmcritchie/excel/insrtrow.htm). I would like to attach the code to a particular cell, so that it always runs from that cell. In the example below, you will see in cell C11 the text "Insert New Row". I would like to ensure that the code runs when this text is clicked. This would ensure that the user always adds the new row to the end of the category (i.e. the row above where the "Insert New Row" text appears). Obviously, if a new row is added, the text will move down, so I can't just reference the current cell. Is there any kind of OnClick of Cell event?

2. Currently, M6:M10 has a named range defined, which is then refered to in M11. If I insert a new row at the bottom of this category, this row is not included in the named range. Is it possible to update the named range to include the new row?
Test_Ashleigh.xls
CDEFGHIJKLM
3Category
4QuantityPricePerPersonTotal
5Food:
6TestFoodItemNo.1410.0040.00
7TestFoodItemNo.2115.0015.00
8TestFoodItemNo.3 
9TestFoodItemNo.4 
10TestFoodItemNo.511.001.00
11InsertNewRowFood:SubTotal56.00
12Miscellaneous:
13TestMiscellaneousItemNo.1210.0020.00
14TestMiscellaneousItemNo.2 
15TestMiscellaneousItemNo.3 
16TestMiscellaneousItemNo.4 
17TestMiscellaneousItemNo.5 
18InsertNewRowMiscellaneous:SubTotal20.00
Sheet1


Thanks in advance for any advice or pointers.

Graham
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
BUMP! Seems it could be simple for a VBA guru, and deserves an answer, so, I am bumping this up.
 
Upvote 0

Forum statistics

Threads
1,224,416
Messages
6,178,513
Members
452,853
Latest member
philipnjk64

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top