puzzeled

Hi all!! I have a database that I have been using and updating for a while now and started having issues after adding another column of information to the database but can't figure out whats going...

excel

Frank Spata

Copper Contributor

May 14, 2018

Matt Mickle wrote:
When you use CurrentRegion it selects all of the cells in this range A1:AA2139. Cells in this range include merged cells L8:O8, W6:X6, W8:X8. It appears that you want to sort only A1:I2139. In order to do this just change the sort code to this:
   .Range("A1:I" & NR).Sort .Range("A1"), xlDescending, Header:=xlYes, _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
I think CurrentRegion is also used in sorts in a few other places in your code. You will most likely need to correct all of the instances of this issue.

Hope this helps.

Matt,

Thank you, that was it! Just ran into another snag that I just found after using the "Delete" button in my form. It does what it's supposed to do but I have flags setup in Q40 to 50 range that I use to determine win or loss "Winner" or "Nope" highlighted in yellow. How do I get them to stay synced after a delete? Part of the formula becomes "#Ref!" along with my conditional formatting! Didn't have to delete anything before now lol! TIA

Matt Mickle

Bronze Contributor

May 14, 2018

Try using ClearContents instead:

rng.ClearContents

Frank Spata
Copper Contributor
May 14, 2018
Instead of the rng.Delete Shift:x1up?
- Matt Mickle
  Bronze Contributor
  May 14, 2018
  Yes. That's correct.
  
  When you delete cells it deletes all of the cells.... therefore all references to these cells turn to #REF. If you use clear contents instead your formulas will stay intact. By clearing contents you only "clear" the values in the cells rather than deleting them all together.
  - Frank Spata
    Copper Contributor
    May 14, 2018
    Ok, but the data doesn't shift up for B2:I2 after deleting.

Forum Discussion

puzzeled

Resources