Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Userform Data

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Any thoughts?

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon,

    Thanks so much, works great!


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •