+ Reply to Thread
Results 1 to 9 of 9

Double sort current selection and delete certain lines

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    germany
    MS-Off Ver
    Excel 2013
    Posts
    4

    Double sort current selection and delete certain lines

    Hi everyone!

    I have been trying to create a macro that does the following:
    1. Sort current selection according to the cell color in row 1 (either white or yellow)
    2. Sort current selection according to the values in row 3 (ascending)
    3. Delete columns (only in the selection) where the color in row 1 is not yellow (the one used in conditional formatting)
    4. Shift cells up

    What I have so far is:

    Please Login or Register  to view this content.
    I am, however, lost on how to sort in row 3 (I have read of the 'offset' function but don't really know how to implement that) as well as on how to delete the columns i don't want...

    Any help would be greatly appreciated!

    Many thanks in advance

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Double sort current selection and delete certain lines

    What do you mean by sort on row 3? Are your headers in row 3?

    Do you have a sample file that you can upload?

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    germany
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Double sort current selection and delete certain lines

    Hi arlu!

    Sorry, I mixed columns and rows... (slightly embarassing, I know^^)

    So what i meant was, I would like to double sort my selection on:
    1. the color of cells in column 1
    2. the values in column 3 (ascending)

    I attached a sample, as you asked me to.

    many thanks for your help!

    sample.xlsx

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Double sort current selection and delete certain lines

    This is the sort code -
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Double sort current selection and delete certain lines

    I was working on the remaining portion of your code and it was giving me issues with that color that you have chosen.

    Is it ok if i put a condition that all rows which are not equal to 1 should be deleted?

  6. #6
    Registered User
    Join Date
    11-07-2012
    Location
    germany
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Double sort current selection and delete certain lines

    wow, first of all, THANK YOU! :D

    yes, deleting all rows unequal to 1 would be perfect!

    It looks like you set fix values for sorting the columns A and C, is that right?
    The problem is that I would require the columns to be the first and third columns of my current selection, is that possible?

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Double sort current selection and delete certain lines

    You are welcome.

    Are there chances of the selection changing?

  8. #8
    Registered User
    Join Date
    11-07-2012
    Location
    germany
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Double sort current selection and delete certain lines

    what do you mean by
    Are there chances of the selection changing?
    The Columns and the amount of rows included Change, however, I always Need to sort on the first and third column, is that what you meant?

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Double sort current selection and delete certain lines

    You mentioned this -
    The problem is that I would require the columns to be the first and third columns of my current selection, is that possible?
    By that statement, i understood that sometimes your data might be in columns A to C, sometimes B to D, etc...as you said current selection.

+ 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