VBA to sort by first character of cell

[mr_hiboy]

Hi,

I've created a macro to sort by two columns. This first column i'd just like to order by the first character which is a number, then by the second column.

Any ideas would be great. Thanks

ActiveWorkbook.Worksheets("FleetChanges").Sort.SortFields.Add Key:=Range( _
"F8:F35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

ActiveWorkbook.Worksheets("FleetChanges").Sort.SortFields.Add Key:=Range( _
"J8:J35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

 

[Luke M]

What you've described is how sorting normally works...I'm not sure what the problem is?

 

[mr_hiboy]

for example

2 Bed - 04.........£699
2 Bed - 07.........£599

I want to show as:

2 Bed - 07.........£599
2 Bed - 04.........£699

So sort by the 2 then by the price, ignoring the 04 & 07.

Cheers

 

[Luke M]

I'd recommend creating a helper column with formula:
=LEFT(A2,1)

And then sorting on the helper column first.

 

[mr_hiboy]

Was kind of hoping you wouldn't say that.

It would take a lot work to add another column without messing up a lot other (older) formulas.

Thanks

 

[NARAYANK991]

Hi Paul ,

I do not know how helpful this is , but here goes :

1. Use conditional formatting to colour the cells which have the text such as 2 Bed - 04.........£699 different colours based on the formula : =LEFT($F8)="2" ; insert different rules based on the number of different starting characters ; if there are more than 4 , you cannot use this technique.

2. Now you can sort this column on colour , and since all the cells are coloured based on their starting character , you will get the desired sort order.

3. You can add a second level , and use the other column which contains the prices.

Narayan

 

[mr_hiboy]

Cheers guys.

Went for the helper column as there would be 6 different possibilities.

Thanks again