+ Reply to Thread
Results 1 to 4 of 4

sort five columns - individually

  1. #1
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    sort five columns - individually

    VBA gurus,
    By being so active here, I am learning some VBA coding. Mostly by copying and modifying code to suit my needs.
    I needed to figure out a way to:
    1. Look at five columns [each is filled to different size by another macro],
    and if row 1 in the respective column has text in it,
    2. Find last used row in that column and
    3. Sort each column alphabetically from Row 4 to end of that column.

    In the attached example I have a macro "sort".
    It works, but as you can see, I am using a lot of workarounds.
    In hopes of learning proper syntax; to loop though columns B-F and selecting appropriate range and then sorting the range; I am asking someone to look at the code and streamline it so its more efficient.

    Here's the code:
    Please Login or Register  to view this content.




    Thank you for you help.
    modytrane.
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello modytrane,

    I scaled your macro down. I am not sure where you are getting the column number from. In the macro, the column is taken from the ActiveCell. You will propbably need to change that. When using Cells the row must be a long value. The column can be either a string representing the column, like "A", "B", "C", etc. or long integer. It is easier to read the string, but faster to use the number in loops. That why the If...Then statements are gone. You also don't need to select a range in order to sort it.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Sort Five Columns

    Leith,
    Thanks for responding.
    A couple of issues with your code.

    You have not declared or defined "Begin". You are using it in

    Please Login or Register  to view this content.
    I also wanted to loop through columns B to F.
    The columns would be fixed as B,C,D,E and F.
    Only rows would vary from 4 to Lastrow. I think you've got that part right.
    The reason I had those IF statements was to create a "For col = 2 to 6" loop. But I didn't know how to use that integer in the sort statement. So I created col1 and made it B,C,D,E or F while I looped through col values of 2 thru 6.

    Thanks Again,
    Hopefully you will have time to incorporate these changes for me.
    modytrane.

  4. #4
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: sort five columns - individually

    Hello VBA Experts,
    I was hoping someone would have picked this up by now.
    I would appreciate if you can look into the code posted above and respond to the request.
    Thanks in advance,
    modytrane

+ 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