+ Reply to Thread
Results 1 to 6 of 6

Sorting non-empty cells to bottom

  1. #1
    Registered User
    Join Date
    10-09-2012
    Location
    Mesquite, Texas, USA
    MS-Off Ver
    Excel 2010
    Posts
    48

    Sorting non-empty cells to bottom

    I'm new here, so please bear with me. I'm working on a excel schedule for work. The cells contain a formula that populates the employee name if they are scheduled to work, and leaves a " " if they are off duty. I used the VBA recorder to perform the sort, but when I run the macro to sort the list, the non blank cells do not sort. The intention is to sort the columns by seniority using a custom list, and move non-blank cells to the bottom of the column. It sorts by seniority, but the non-blank cell do not move to the bottom. I tried sorting by color, but that didn't work with VBA. I also thought about filling the cells with zzzz and conditionally formatting the text color to match the cell color, but I would like to find another solution. Any help is appreciated.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Sorting non-empty cells to bottom

    Your formula that returns " " if the employee is off duty should return "" instead - the first returns a space, whereas the second returns an empty string (effectively an empty cell).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-09-2012
    Location
    Mesquite, Texas, USA
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Sorting non-empty cells to bottom

    Pete_UK,
    I tried that but that didn't solve my problem. I'm attaching a portion of the file. The workbook contains a roster for each day of the month, and refers back to a main schedule. If an employee is scheduled to be on duty, it adds their name and if not it leaves the cell blank. What I'm trying to do is sort each column by seniority, and if there are not enough employees on duty for a given time period, it changes the cell fill to yellow to highlight slots when overtime is needed. The highlighting works fine, but I'm having problems with the sorting. I used the macro recorder to record the macro to sort and highlight and ran it on the first 2 days. There are times (especially on the second day) when it doesn't sort the "blank" cells to the bottom, then it highlights that cell to indicate that overtime is needed when in fact there are enough employees on duty. I hope this makes sense.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-25-2012
    Location
    Chennai,TamilNadu,India
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    61

    Cool Re: Sorting non-empty cells to bottom

    Hi,

    I tweaked your macro a little bit and reached to a solution. To execute my macro, press Ctrl+m. As per my procedure the blanks are kept at the bottom.

    I executed across the sheets it worked. Check it out at your end and let me know whether this was the one you were looking for...

    Regards,
    Hariharan
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-09-2012
    Location
    Mesquite, Texas, USA
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Sorting non-empty cells to bottom

    Hi
    I'm still having problems with the sorting. It seems to be related to VBA only because if I do a manual sort, then the blank cells go to the bottom as intended. I rewrote the macro to look for cells that haveformulas and value=" " and to clear contents of those cells so that they are truly blank and would sort to the bottom. The macro to clear contents works, but the sort does not. Not only are the blank cells not sorting to the bottom, but the sort is sorting in ascending order instead of the custom sort list. The macro is Easy_Button and the shortcut is Alt Shift M. Thanks for the help.
    Attached Files Attached Files
    Last edited by coopman64; 10-10-2012 at 09:25 PM. Reason: Edited because wrong file was attached

  6. #6
    Registered User
    Join Date
    10-09-2012
    Location
    Mesquite, Texas, USA
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Sorting non-empty cells to bottom

    Just a quick update. I read through the macro and noticed that the sort said yes to headers but that wasn't included in the range for the sort, so I corrected that. It still wont sort empty cells to the bottom...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1