+ Reply to Thread
Results 1 to 11 of 11

I have a sort macro. How to add script to preselect rows to sort based on column value?

  1. #1
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    I have a sort macro. How to add script to preselect rows to sort based on column value?

    I have a macro which performs a sort for me.

    I have it set up to perform the sort on whatever rows are preselected by the user.

    I would like to have the macro preselect the rows to sort for me, based on whether there is any value in a certain column (it will always be the same column, 'G'). All cells in column G presently contain either nothing, or a number between 1 and 20. I prefer the macro select rows based on whether there is any value in cells in column G at all, as I may add additional numbers later.

    EDIT: Oh, and if it helps, all of the rows with a value in them will always be contiguous to one another. All of the blank cells are at the top and bottom (but those exact locations vary.)

    This is what I'm using that I want to add on to.
    Please Login or Register  to view this content.
    Thank you for your help. I'm a total rookie at this VBA stuff. But at my small firm, I'm the default "techie" by virtue of everyone else being even more clueless than I am. lol
    Last edited by Leith Ross; 03-05-2014 at 09:59 PM. Reason: Changed Quote tags to Code Tags

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    1) Fetch row numbers:
    Please Login or Register  to view this content.
    Or,
    2) Fetch address:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 03-05-2014 at 10:22 PM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    Thanks... Now bear with me... Where do I insert that code into the existing code?


    In front of the first line of code?

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    Since I don't know what your worksheet looks like, assume that the sort will ALWAYS include columns A - H, with rows determined by G. Then
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    I don't know why, but that didn't work either... For now, I made it work via manipulating my sheet... I numbered every row in column G (the 'blanks' are numbered in white, so it's invisible to the user) in such a way that if I filter the sheet before sorting it, I consistently get the result I need.

    I was hoping for a script that would look at cells in column G, and would select entire rows with a value present in the 'G' column cells, and not select 'blank' cells. Numbering the 'blank' cells is an extra step I would rather not have to do, but having a sort macro is still much more time efficient than the current method of building a sort every time it needs done, which is frequently.


    I'll post an actual sheet I'm sorting later... I have make a generic version that I can post publicly first.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    In post #1: "... EDIT: Oh, and if it helps, all of the rows with a value in them will always be contiguous to one another. All of the blank cells are at the top and bottom (but those exact locations vary.) ...". However, "... I was hoping for a script that would look at cells in column G, and would select entire rows with a value present in the 'G' column cells, and not select 'blank' cells. ..." seems to indicate that there may be blank rows interspersed??

  7. #7
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    No. The blanks in column G are only at the top and bottom. The rows that will need sorted are always contiguous to one another... They are next to each other without interruption. Instead 0f the user having to grab all 0f those rows and then triggering the sort macro with CTRL+M, I want the user to be able to just hit CTRL+M, and have Excel find the rows and sort them all in one sub.

    This is basically being used to sort a list 0f products and prices that can be just tens 0f lines long, or thousands, depending on the project. Obviously, it would be great to just hit a button and sort the list (the sort is used to group like products, so that those sub groups can be easily shown to the buyer), rather than having to scroll to grab all 0f those lines.
    Last edited by Jasonhouse; 03-08-2014 at 11:29 PM. Reason: because the word o-f is being censored?

  8. #8
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    Surely there is a way to have Excel look at column G and any time there is a value in a cell, excel would select that row. I figured it would require an if/then query. Something that starts at G1. If there is a value in cell G1, then Row 1 is selected. Then it would go to G2 and repeat, until the entire sheet had been checked, then it would perform the sort I've already got.
    Last edited by Jasonhouse; 03-08-2014 at 11:53 PM.

  9. #9
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    double post.
    Last edited by Jasonhouse; 03-09-2014 at 12:45 AM. Reason: please delete

  10. #10
    Registered User
    Join Date
    03-05-2014
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    I found a script that successfully will select entire rows based on whether cells in column A have a "G" as cell content. naturally, I'm not experienced enough to know how to adapt this to what I'm doing...

    Please Login or Register  to view this content.
    Surely it's not all that complicated to adapt this code to select rows based on whether cells in column G have a value (>0) instead, and add it to the existing sort macro up top?

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: I have a sort macro. How to add script to preselect rows to sort based on column value

    Two changes
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro for sort rows after value in certain column
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-26-2014, 06:47 AM
  2. script for sort z to A in a column
    By saranyabi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2013, 11:40 AM
  3. Copying rows from other sheets, and sort them based on a priority column
    By BKevinT in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-26-2010, 11:59 AM
  4. Replies: 2
    Last Post: 11-15-2005, 12:10 PM
  5. Replies: 0
    Last Post: 11-14-2005, 09:25 PM

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