Excel VBA to sort columns that can appear in different locations on a worksheet

  • I have the need to sort columns of data that appear at different locations on a worksheet. I would like to click on the first column and have the program sort that one and the rest, one column at a time.

  • Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


    Remember to desensitize the data.

  • I am trying to use the range.sort method. I am having trouble getting the range to work (I think, at least that is where most of my errors point). I am thinking that the range needs to vary for each instance.


    I am trying to use code like this:

    '

    Set sortcolumn = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(210, 0))

    '

    sortcolumn.Sort order1:=xlAscending, Header:=xlNo

    '

    and get an error (Sort method of range class failed). I have tried many different methods trying to define the range, but they all seem to fail for some reason of another. I think i am missing something (probably something simple or basic), but I do not know what. Any help you can give or point me to would be appreciated.


    Thanks.

  • Welcome to Ozgrid. An example workbook would be helpful in providing better code.


    Maybe you mean


    Code
    Dim SortColumn As Range
    Set SortColumn = Range(ActiveCell, ActiveCell.End(xlDown))

    Relying on ActiveCell is not a good idea, in case the user has selected the wrong cell.


    Also, please take a few minutes to read the Forum Rules to understand why I have added code tags to your post.

  • The way I understand it: ...ActiveCell.End(xlDown) will stop at the first blank cell, it is possible that the column will contain blanks before the end.


    I have not been able to get Range.sort to accept SortColumn as a range.


    Mike

  • The way I understand it: ...ActiveCell.End(xlDown) will stop at the first blank cell, it is possible that the column will contain blanks before the end.


    I have not been able to get Range.sort to accept SortColumn as a range.


    Mike

    An example workbook would save guess work! It has been suggested twice now.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!