Expand a range when you click any cell in the last line of the range

**Dickie Dunkin** · 04-06-2016, 01:38 PM

I would like the data entry portion of my worksheet to expand automatically when you try to make an entry on the last available line.
The attached spreadsheet is a portion of a workbook used to calculate how much concrete I need to do a job.

I have the following named ranges:
SlabData=E6:P9 (This named range may not be needed)
SlabDataLastRow=E9:P9
Note that shaded cells contain formulas. Everything in the last row needs to be copied into the new rows.
Note that there are merged cells that need to be copied.

Clicking in any cell in "SlabDataLastRow" should do the following:

Insert three full rows (not cells) above row 9 - (I would like to be able to edit the VBA code to change this from 3 to any value such as 1,2,4 etc.)
Copy the original entire row 9 and paste it into all of the new rows.
Activate the cell that was clicked in the first place. From the user's perspective three new rows will magically appear below the active cell.
The Name "SlabDataLastRow" will now refer to E12:P12 and SlabData=E6:P12

Thanks in advance,

Rob

**Bernie Deitrick** · 04-06-2016, 02:43 PM

USe this event code in your worksheet's codemodule

Please Login or Register  to view this content.

**jaslake** · 04-06-2016, 02:49 PM

Hi Rob

Try this...

Please Login or Register  to view this content.

**Dickie Dunkin** · 04-06-2016, 06:54 PM

Bernie - thank you for the quick reply!

There's just one small hiccup. The cell E9 is actually a merged cell composed of E9 and F9. For whatever reason, clicking in E9 does not trigger the code. Clicking in any cell G9:P9 works exactly as desired.

I was hoping to duplicate the code several times on the same worksheet. I am going to create other named ranges such as "PitDataLastRow", "MaterialsDataLastRow","RentalDataLastRow" etc. and thought I would be able to duplicate the code for each named range, but it doesn't work that way.

When I duplicate the code with "PitDataLastRow" I get "Compile error: Ambiguous name detected: Worksheet_SelectionChange".

I now see that a worksheet can have only one "selection change".

Please see if you can still help me in light of this new requirement.

Thanks again,

Rob

**Dickie Dunkin** · 04-06-2016, 07:03 PM

jaslake - thanks to you too for the quick reply!

Your code works great! I double-click in any cell in the range (even merged cells) and three new rows appear. I know how to change the code from a double-click to a single click, but then I have the same problem I described to Bernie in my previous reply.

As you may see from my last reply, I'm trying to make multiple named ranges auto-expand.

When I duplicate the code with "PitDataLastRow" I get "Compile error: Ambiguous name detected: Worksheet_BeforeDoubleClick".

Please see if you can come up with a method of handling multiple named ranges on the same worksheet.

Thanks again,

Rob

**Bernie Deitrick** · 04-06-2016, 07:03 PM

The failure with merged cells is because of the multi-cell check that I use, so I took that out.

For the multiple areas, try the code like this: I hope you see the pattern to be able to expand it.

Please Login or Register  to view this content.

**Dickie Dunkin** · 04-06-2016, 07:25 PM

Thanks! That's exactly what I wanted and I'm sure I can duplicate it as needed.

I really learned something here - you just nested all of the code under a single "Worksheet_SelectionChange" event - no need for each range to have its' own event.

You also fixed it so that clicking in a merged cell triggers the code.

Thanks,

Rob

**Dickie Dunkin** · 04-17-2016, 11:16 AM

Bernie - I've hit a snag! Please see the newly uploaded spreadsheet.

Each row contains several formulas that duplicate the value in the cell above.

For example: In the range "SlabDataLastRow" The formula in H12 is =H11
After the code runs the formulas are not correct. With code set to insert 3 rows, H12 becomes H15, but the formula in H15 still reads =H11 when it should read =H14. When this incorrect row is copied to the three rows above, those new formulas are incorrect as well. The "new" H12 reads =H8 when it should read =H11.

The present code copies the row that contains "SlabDataLastRow" and then inserts three rows above.
I think what it needs to do is insert the three rows FIRST (this will make the equations change) and then copy the last row into the three blank rows.

Thanks in advance!

Rob

**Bernie Deitrick** · 04-17-2016, 12:27 PM

Try changing each instance of

.EntireRow.Copy

.EntireRow.Resize(iRowCount).Insert

to

.EntireRow.Resize(iRowCount).Insert

.EntireRow(-iRowCount).Copy .EntireRow(-(iRowCount - 1)).Resize(iRowCount + 1)

**Dickie Dunkin** · 04-17-2016, 02:04 PM

That worked! Thanks again!

**Dickie Dunkin** · 04-17-2016, 03:23 PM

One last glitch....

Inserting rows works as designed and Row 12 is now row 15 since I'm adding 3 rows at a time.
Now, if you want to UNDO adding the rows, select rows 12,13,14 and delete the entire rows.
Cell H12 now reads #REF!
I think this is due to the fact that rows 12,13,14 are still selected, and the selection now includes the range "SlabDataLastRow"

Thanks,

Rob

**Dickie Dunkin** · 04-17-2016, 03:32 PM

More information - the problem is not what I thought it was.
The true formula in H12 is =IF(OR($O12>0,G12>0),H11,"") which normally "boils down" to =H11.

However, if I delete any row, say row 8, H8 will now read =IF(OR($O8>0,G8>0),#REF!,"") instead of =IF(OR($O8>0,G8>0),H7,"")

Please advise.

Rob

Expand a range when you click any cell in the last line of the range

LinkBack

Thread Tools

Rate This Thread

Display

Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Re: Expand a range when you click any cell in the last line of the range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] expand the range of a named range

I need to select a range on a cell click

Auto Expand Cell - Double Click

Auto expand range based on first cell selection

Concatenate a range rather than click each cell

Cannot Expand Named Range - when size of the Range exceeds

click on a cell to expand with more information

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions