Userform Data

GBOB

New Member
Joined
Mar 6, 2002
Messages
17
Hi all,

Posting this again, please tell me if the question is not clear.

I have created a userform that dumps data into a named range. Each time I add new data the data range is increased by one row. To the right of the data range I have a number of formulas that perform functions on the particular data range row. My problem is that when I am editing my data entries and I delete an entry therefore deleting a row in my data range all of the formulas to the right of the row turn into #REF! permanently, is there any way to stop this?

Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi GBOB,

You have deleted the cells that the formulas reference, so naturally the formulas no longer give a valid result. But you didn't mention what you want them to do. How should they accommodate to the fact that their data is now missing? Do you want them to now reference the cells that have moved up to replace the deleted cells? Or perhaps you want to delete the formulas? Or maybe you want to leave the formulas there but want them to yield a blank rather than an error?
 
Upvote 0
Hi Damon,

Thank you for the response. I would like the formulas to now reference the cells that have moved up to replace the old cells that were deleted. Any thoughts?

Thanks!
 
Upvote 0
Hi again GBOB,

Okay, say your range that contains the cell you are deleting is A1:A100 and the range that contains formulas dependent on this data is in B1:B100. To re-align all the formula references, simply use the FillDown method:

Sheets("MySheet").[B1:B100].FillDown

If you don't know where the data in the B column ends (perhaps because you are constantly adding or deleting rows), simply use:

With Sheets("MySheet")
.Range(.[B1],.[B65536].End(xlUp)).FillDown
End With

If the sheet is the active worksheet, then this becomes just:

Range([B1],[B65536].End(xlUp).FillDown

Simply apply this general idea to whatever ranges your data are in.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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