Forum Discussion

Frank Spata's avatar
Frank Spata
Copper Contributor
May 11, 2018

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's avatar
Frank Spata
Copper Contributor
May 12, 2018

Still a problem. Here is a link to the file. https://www.dropbox.com/s/fysi14j70lespbl/test2.xlsm?dl=0

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 14, 2018

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.

 

 

  • Frank Spata's avatar
    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's avatar
      Matt Mickle
      Bronze Contributor
      May 14, 2018

      Try using ClearContents instead:

       

      rng.ClearContents
      • Frank Spata's avatar
        Frank Spata
        Copper Contributor
        May 14, 2018

        Instead of the rng.Delete Shift:x1up?

Resources