+ Reply to Thread
Results 1 to 8 of 8

Macro sorting multiple sets of columns

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Macro sorting multiple sets of columns

    Hello good people!
    I have made a form for bird population used as reference to our result program. We have seen that it is a problem for people to deliver a useful list of birds and this make us use/waste a lot of time to sort things out when we update this with our result-program. So I have am trying to make a form to eliminate the problem. I have translated my form to English as best I can.
    My problem is a macro that sort multiple sets of columns in a certain way. It is possible to edit in all cells in the columns in the form. The sorting must be in a certain way, like this ; 1. A/U, 2.Year, 3.Ringnr., 4.Club. And like this in all 3 sets of columns. (A means male and U means hen).
    I need the sorting to be in a way that wherever the member write the information of his birds in the form, it is sorted and eventually moved to the left set of columns, when that is full it continues to the middle set of columns and then the right set of columns. And at the same time also sorted as described over with 1., 2. etc. The sorting is done whenever the member press the button "Sorting". I have also made a keyboard shortcut for the same function, Ctrl+s.
    I have made a macro that works well in the left column, but I am not able to make it work with all three sets of columns as I need to. Whatever I try out in my macro it fails. I am out of ideas and knowledge.
    I would appreciate help with this. If anything is unclear I am at your disposal.
    I send as attachment the whole file, translated to English and unlocked. There is formulas in some cells and I guess you will see that. I case if the macro did not follow the file, I have copied it in the word-file.
    Sub Sorter.docxPigeon population Form 2.xlsm

  2. #2
    Registered User
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Macro sorting multiple sets of columns

    Try this and see if it does what you want

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Macro sorting multiple sets of columns

    Excel cannot sort multiple ranges in different columns (your 3 sets) as one set of data. The macro could be made to copy all the data from the 3 sets to another temporary set of columns so that all three set are in the same columns. Then sort. Then copy all the data back so that the data are filled from left to right filling the left set first.

    Another problem is your first data set (B5:G59) has columns C and D merged. This creates a big pain-in-the-a$$ programmatically because it has a different number of columns than the other two sets. You could make the macro a lot easier if you could design the form without any merged cells for the three data set areas.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Re: Macro sorting multiple sets of columns

    Dear ihmha. I have tried your macro and Your macro works perfectly for sorting each set of columns separately. But that was however not what I had in mind. I wanted the three set of columns to be sorted as if they where one set of column. I need it to like this with three sets of columns for a useful print out, with all on one side. I never the less, thank You for your effort, Sir.

    Dear AlphaFrog. You have understood what I need on the spot. I did not know that the merged cells made trouble, but after I read your post, I can understand that it actually does. I made that only for making it prettier where the member name is on the top of the sheet. I have now deleted the extra column and there is no longer any merged columns in the sheet. All sets of columns is now made of the same amount of columns. I found another solution to make it nice at the top of the sheet.
    I attach a new file with no merging of columns. I hope you will still give me a hand with this. On forehand, thank You.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Macro sorting multiple sets of columns

    This uses AB6:AG167 to temporarily hold and sort the data from the three sets

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Re: Macro sorting multiple sets of columns

    Dear AlphaFrog. Yesss, this works very good. Exactly what was needed. It was like magic for me to see your work in action after me working with this since june. The "Sorting" button works just fine.
    But, if I may, one last question, how do I implement a keyboard shortcut, now after the macro is already made. I have tried editing in the macro file different things, but I cant get it to work right.
    To have a keyboard shortcut is almost required as members also have Labtops without using any mouse. Keyboard shortcut; Ctrl+s

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Macro sorting multiple sets of columns

    You're welcome.

    The shortcut is defined from the Options button on the Macros dialog
    https://support.office.com/en-us/art...__toc270927002

  8. #8
    Registered User
    Join Date
    10-02-2014
    Location
    Norway
    MS-Off Ver
    2016
    Posts
    16

    Re: Macro sorting multiple sets of columns

    Dear Sir! Thank You for the last information. Now all works like a well oiled machine. Again thank You for great work and very quick! Awesome!

+ 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. Macro for sorting multiple columns in a range
    By jnewby in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2015, 03:24 AM
  2. [SOLVED] Compare multiple sets of columns
    By bseo927 in forum Excel General
    Replies: 7
    Last Post: 08-19-2013, 01:16 PM
  3. Comparing multiple sets of columns for greater values
    By metsfan666 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-09-2012, 08:41 PM
  4. Hide/Unhide Multiple Sets of Columns with Toggle Button
    By Mischief433 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-06-2012, 05:55 AM
  5. [SOLVED] Macro for sorting multiple columns to row / column combination
    By daryln in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2012, 12:33 AM
  6. To convert data in multiple sets of columns to multiple rows
    By Dhanya_nair in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2012, 02:49 PM
  7. macro for sorting data from one column to multiple columns
    By hck in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2012, 08:44 AM

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