Hi I want write a macro to sort a spreadsheet with the rows being ordered
from a particular collumn which contain the following letters: W, SA, SA+, ST
and blank cells. I need the rows to be sorted in that order from top to
bottom. I.E. a;; the `W` rows first, all the ` SA` rows next etc.

Now, I know I could create a collumn next to it with perhaps a vlookup
reference of say W=1. SA =2 etc, and then sort on that instead, but belive it
or not, I`m almost out of collumns (it`s a big spreadsheet) and also some of
my other refences might be affected if I go and plonk another collumn in now.
Any ideas?
Thanks guys
RR1

Romileyrunner1,

Enter your values in the desired order in a block of cells, and select those cells. Then use Tools
/ Options.... Custom Lists Import OK.

Then select your data, use Data / Sort... Options.... and select your new list from the available
lists.

HTH,
Bernie
MS Excel MVP


"Romileyrunner1" wrote in message
...
Hi I want write a macro to sort a spreadsheet with the rows being ordered
from a particular collumn which contain the following letters: W, SA, SA+, ST
and blank cells. I need the rows to be sorted in that order from top to
bottom. I.E. a;; the `W` rows first, all the ` SA` rows next etc.

Now, I know I could create a collumn next to it with perhaps a vlookup
reference of say W=1. SA =2 etc, and then sort on that instead, but belive it
or not, I`m almost out of collumns (it`s a big spreadsheet) and also some of
my other refences might be affected if I go and plonk another collumn in now.
Any ideas?
Thanks guys
RR1

You could do a multiple Find & Replace on that column in your macro to
replace ST with 4, SA+ with 3, SA with 2 and W with 1, then do your
sort, and then re-apply your Find & Replace backwards if you want to
keep those codes.

Hope this helps.

Pete

On Sep 8, 7:49*pm, Romileyrunner1
wrote:
Hi I want write a macro to sort a spreadsheet with the rows being ordered
from a particular collumn which contain the following letters: W, SA, SA+, ST
and blank cells. I need the rows to be sorted in that order from top to
bottom. I.E. a;; the `W` rows first, all the ` SA` rows next etc.

Now, I know I could create a collumn next to it with perhaps a vlookup
reference of say W=1. SA =2 etc, and then sort on that instead, but belive it
or not, I`m almost out of collumns (it`s a big spreadsheet) *and also some of
my other refences might be affected if I go and plonk another collumn in now.
Any ideas?
Thanks guys
RR1

Under Tools-Options - Lists, you can create a Custom List telling XL how you
want certain things ordered. This will create a custom list.

Then, if you were to go to Data - Sort, under Options, you can choose which
custom list you want to sort by. In VB, this will end up looking something
like this:

Selection.Sort Key1:=Range("C12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

The key part is "OrderCustom:=6" as this is saying to use the 6th Custom
List. You will need to modify this to fit your actual settings.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Romileyrunner1" wrote:

Hi I want write a macro to sort a spreadsheet with the rows being ordered
from a particular collumn which contain the following letters: W, SA, SA+, ST
and blank cells. I need the rows to be sorted in that order from top to
bottom. I.E. a;; the `W` rows first, all the ` SA` rows next etc.

Now, I know I could create a collumn next to it with perhaps a vlookup
reference of say W=1. SA =2 etc, and then sort on that instead, but belive it
or not, I`m almost out of collumns (it`s a big spreadsheet) and also some of
my other refences might be affected if I go and plonk another collumn in now.
Any ideas?
Thanks guys
RR1

Hi,

You need to build a custom list like this

Within Excel, click Tools and click Options.
Select the Custom Lists tab.
Under Custom Lists, verify that NEW LIST is highlighted.
In the List Entries field, start typing in your list elements in the order
they should be sorted. Make sure you press Enter after each element.
Once you have typed in all the elements, press Add.
Click OK.

Now select your data and then

Data, sort, Custom and select the list you created and sort your data

Mike


"Romileyrunner1" wrote:

Hi I want write a macro to sort a spreadsheet with the rows being ordered
from a particular collumn which contain the following letters: W, SA, SA+, ST
and blank cells. I need the rows to be sorted in that order from top to
bottom. I.E. a;; the `W` rows first, all the ` SA` rows next etc.

Now, I know I could create a collumn next to it with perhaps a vlookup
reference of say W=1. SA =2 etc, and then sort on that instead, but belive it
or not, I`m almost out of collumns (it`s a big spreadsheet) and also some of
my other refences might be affected if I go and plonk another collumn in now.
Any ideas?
Thanks guys
RR1

Fantastic work LUKE M.
Made my day that has fella.
Thanks a lot.
RR1

"Luke M" wrote:

Under Tools-Options - Lists, you can create a Custom List telling XL how you
want certain things ordered. This will create a custom list.

Then, if you were to go to Data - Sort, under Options, you can choose which
custom list you want to sort by. In VB, this will end up looking something
like this:

Selection.Sort Key1:=Range("C12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

The key part is "OrderCustom:=6" as this is saying to use the 6th Custom
List. You will need to modify this to fit your actual settings.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Romileyrunner1" wrote:

Hi I want write a macro to sort a spreadsheet with the rows being ordered
from a particular collumn which contain the following letters: W, SA, SA+, ST
and blank cells. I need the rows to be sorted in that order from top to
bottom. I.E. a;; the `W` rows first, all the ` SA` rows next etc.

Now, I know I could create a collumn next to it with perhaps a vlookup
reference of say W=1. SA =2 etc, and then sort on that instead, but belive it
or not, I`m almost out of collumns (it`s a big spreadsheet) and also some of
my other refences might be affected if I go and plonk another collumn in now.
Any ideas?
Thanks guys
RR1