+ Reply to Thread
Results 1 to 17 of 17

Data sort, empty formula cells to bottom merged cells!

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Data sort, empty formula cells to bottom merged cells!

    Hi,

    I have a made a list and it contains this formula: =Machinelijst!B8
    Because there are colloms next to this collom I used this to sort the colloms together:
    Please Login or Register  to view this content.

    But there a few cells that still empty or a "0" I hided this "0" by go options-> advanced->.. don't show 0' in formulas.
    But if I want to sort this kolloms it puts the empty ones at the top.

    Is there a way to make them got to the bottom?


    Greetings
    Ikkuh!
    Last edited by ikkuh; 04-08-2013 at 06:59 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Data sort, empty formula cells to bottom

    You can reverse the sort order to descending rather than ascending.

    Please Login or Register  to view this content.
    Last edited by AndyLitch; 04-09-2013 at 03:12 AM.
    Elegant Simplicity............. Not Always

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Data sort, empty formula cells to bottom

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

  4. #4
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom

    Hi,

    But if I do descending its goes from Z->A and not from A-> Z


    thanks for your time

    greetz
    ikkuh


    ps: Sorry zbor forgotten

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,405

    Re: Data sort, empty formula cells to bottom

    Then edit post please if forgot.

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Data sort, empty formula cells to bottom

    1. sort the data descending
    2. Select only the cells with data
    3. Resort Ascending

    That should give you what you're after

  7. #7
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom

    hi,

    it indeed does but, I need to make that into a button and that doesn't work =/

    but thanks


    greetz
    ikkuh

  8. #8
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Data sort, empty formula cells to bottom

    Try this ..

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom

    Hi,

    It gives an error in: error 424 no object
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Data sort, empty formula cells to bottom

    amend the sheet name to match your dataset
    You can get the name from the VBE explorere

  11. #11
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom

    hi,

    I tryed that but then the line turns red =/
    and gives "syntaxis error"

    :/

  12. #12
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom

    Hi,

    Here you can find a look a like file
    as you can see the rows need to stay together

    thanks your time!

    greetz
    ikkuh
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Data sort, empty formula cells to bottom

    Ahhhhhh... you have merged cells....... looks pretty but doesn't work well with the machine

  14. #14
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom

    Hi,

    I indeed noticed before that I had some problems that should not be there cuz of the merged cells =/
    Is there a way to do it with the merged cells?
    Or do I need to change it? =/

    thanks!

  15. #15
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom merged cells!

    Hi,

    I still didn't manage to find this out =/
    any suggestions??

    thanks in advance!

  16. #16
    Registered User
    Join Date
    04-02-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Data sort, empty formula cells to bottom merged cells!

    Hi,

    Is it possible to sort these merged "empty" (they contain a formula with gives a "0" (formula: ='blad3'!B7.......='blad3'!B81)) cells.
    Already spend a few days on this problem =/ can some one plz help??

    Thanks in advance!

  17. #17
    Registered User
    Join Date
    03-29-2013
    Location
    Bangalore/India
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Data sort, empty formula cells to bottom merged cells!

    Very helpful information, worth reading this forum, good work guys, i learnt some new techniques.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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