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
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 11, 2018

You may have cells merged that you don't realize.  Maybe try typing this into the immediate window and confirm with enter.  Then try running the code again.

 

Sheets("Sheet1").Cells.Unmerge

 

You're correct you can't upload macro enabled files due to security risks.  If you still have an issue after trying the above fix you could try uploading it to a third party site like DropBox, Box or FileSnack and posting the link to the community.

  • 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

         

         

         

Resources