Macro won't sort top row after data added to one column.

[klanger79]

I have a macro attached to a button (each sheet actually has 4 macros attached to four buttons... but they are all virtually identical).

Macro to sort range A5:E5 downwards (sorting by column A) works perfectly, until data is added to column D. When any data is added to column D the top row of the range will not sort. Values in column D are formatted as number, to one decimal place.

Would anyone be able to look at the following code and tell me where I'm going wrong?

Just for info, rows 1 to 4 are just 'title' rows and are merged (or not) according to a preferred visual style.

Bit of a macro/code newbie... go easy on me!

Thanks!

Private Sub CommandButton1_Click()
Static iOrder As Integer
Dim oRange As Range
If iOrder = xlAscending Then
iOrder = xlDescending
Else
iOrder = xlAscending
End If
Set oRange = Range("A5:E5")
Set oRange = Range(oRange, oRange.End(xlDown))
oRange.Sort Key1:=Range("A5"), Order1:=iOrder, Header:=x0Guess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Try with Header:=xlNo.

 

[klanger79]

Andrew,

Thank's for the welcome.

Have tried Header:=x1No with no success.

The sort works fine with Header:=x1Guess and Header:=x1No... just so long as there is no data in column D. With data in column D neither option works.

It's puzzling me because column D is virtually identical to columns C & E either side of it. Some small cell formatting differences... and a slightly different cell width... but nothing that should affect the code?

K

 

[Andrew Poulsom]

It's xlNo not x1no (xl for Excel). This worked for me regardless og the contents of columns D:

Private Sub CommandButton1_Click()
    Static iOrder As Integer
    Dim oRange As Range
    If iOrder = xlAscending Then
        iOrder = xlDescending
    Else
        iOrder = xlAscending
    End If
    Set oRange = Range("A5:E5")
    Set oRange = Range(oRange, oRange.End(xlDown))
    oRange.Sort Key1:=Range("A5"), Order1:=iOrder, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

I strongly recommend that you put Option Explicit at the top of your code module. That way typos like x0Guess and x1No will cause a compile error. You can have it inserted automatically in new modules by checking 'Require Variable Declaration' on the Editor tab under Tools|Options in the Visual Basic Editor.

 

[klanger79]

Ah! Rookie mistake.

Working fine now.

Thank you very much for the help.

K