Userform Data

[GBOB]

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!

[Damon Ostrander]

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?

[GBOB]

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!

[GBOB]

Any thoughts?

[Damon Ostrander]

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.

[GBOB]

Damon,

Thanks so much, works great!