Alphabetical Sort but ignore square brackets

If your data was in column A, I would make two separate sub list in B and C for the brackets and non brackets
in B1
=IF(NOT(ISERROR(AND(FIND("[",A1),FIND("]",A1)))),A1,"")
in C1
=IF(B1="",A1,"")
copy down

Then sort both lists, and append column B to the bottom of column C

Cheers

Dave

Thanks Dave

I was trying to avoid adding extra columns because there are all sorts of other things going on in the worksheet that would be messed up by adding columns and I was hoping that there was a way of resolving it  locally.

I did wonder about doing something like adding  zz before the [ doing a sort and then deleting the zz.
Would that or something along those lines work?

Can you give me some code that would do that please?

Thanks

this will work in situ

Cheers
Dave

Sub FindSort()
    Dim rng1 As Range, rng2 As Range, cel As Range, c As Range
    Dim FirstAddress As String
    Dim AppCalc As Long
    Dim Prefix As String

    Prefix = Application.WorksheetFunction.Rept("Z", 10)

    Set rng1 = Columns("A")


    With Application
        AppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set cel = rng1.Find("[*]", , xlValues, xlPart, xlByRows)
    If Not cel Is Nothing Then
        Set rng2 = cel
        FirstAddress = cel.Address
        Do
            Set cel = rng1.FindNext(cel)
            Set rng2 = Union(rng2, cel)
        Loop While FirstAddress <> cel.Address
    End If

    If Not rng2 Is Nothing Then
        For Each c In rng2
            c.Value = Prefix & c.Value
        Next
    End If
    rng1.Sort Key1:=Range("a1"), Order1:=xlAscending
    rng1.Replace Prefix, vbNullString
    With Application
        .ScreenUpdating = True
        .Calculation = AppCalc
    End With

End Sub

Select allOpen in new window

You could also create a custom list  that has A-Z and then [ at the end. You can then sort by this list and your [ data will be at the end.
Regards,
Rory

OK Dave that seems to do the trick thanks.

Just one thing, I should have said I would like to sort the complete row based on column A
Whats the best way to do that?


Rory
The data that’s in Column A is variable so I don’t know the contents of it up front.
How would you create a custom list  that has A-Z and then [ at the end and then sort by this list?
Thanks

Which version of Excel are you using?

its 2007 i have but i would like it to work on 2003 as well if possible.

In 2003, it's Tools-Options-Custom Lists, then type:
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
[

into the box and press Add.

Then when sorting your data, choose Data-Sort, press Options... and change the first key sort order to your custom list.

In 2007, you add the custom list via the Excel Options.

Hi Dave
I am getting a Run-time error '1004'
Applicatiom-defined error on that line

Any thoughts?

to make sure I gave you the right mods :)

Sub FindSort()
    Dim rng1 As Range, rng2 As Range, cel As Range, c As Range
    Dim FirstAddress As String
    Dim AppCalc As Long
    Dim Prefix As String

    Prefix = Application.WorksheetFunction.Rept("Z", 10)

    Set rng1 = Columns("A")


    With Application
        AppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set cel = rng1.Find("[*]", , xlValues, xlPart, xlByRows)
    If Not cel Is Nothing Then
        Set rng2 = cel
        FirstAddress = cel.Address
        Do
            Set cel = rng1.FindNext(cel)
            Set rng2 = Union(rng2, cel)
        Loop While FirstAddress <> cel.Address
    End If

    If Not rng2 Is Nothing Then
        For Each c In rng2
            c.Value = Prefix & c.Value
        Next
    End If
    rng1.EntireRow.Sort Key1:=Range("a1"), Order1:=xlAscending
    rng1.Replace Prefix, vbNullString
    With Application
        .ScreenUpdating = True
        .Calculation = AppCalc
    End With

End Sub

Select allOpen in new window

In fact, it seems you don't need the [ as the last entry in the custom list. Just specifying the alphabet will do it.

Hi Dave

Ok i have got that working on a demo sheet but in my main sheet i only want to do the sort on rows that come after a cell in A12 which has the range title "Subject"
Do i have to change this line
  Set rng1 = Columns("A")
to make it work for all rows after Range("Subject").offset(1,0)

Thanks

Rorya
I cant see where in Excel Options you set this up?
Can you point me in the right direction please?

Thanks

Not had a chance to implement this yet but this is still active
Thanks

Thanks for your help guys....
Got this working now.

Cheers

Gordon