A Sorting Problem

[k1ngr]

In Excel 2007, I'm trying to sort on column A, with headers of :x: and "y".
There is a formula in column A:
=IF(B2="","",B2), then copied down.

I need to have Rows 2-4 (cells that don't display anything, but have a
formula in them) to sort below the cells which display characters. Does
anyone have a suggestion on how to accomplish this?

I am using a command button to initiate the sort - the VBA code for the
command button is shown below the spreadsheet.

A B
1 y z
2
3
4
5 a a
6 c c
7 m m
8 r r
9 x x

--------------------
Private Sub CommandButton1_Click()
'
Application.Goto Reference:="data"
ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range( _
"A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("data").Sort
.SetRange Range("A1:B20")
.Header = xltrue
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[Roger Govier]

Hi

It worked fine for me when I modified to

Private Sub CommandButton1_Click()
'
ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range( _
"A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=
_
xlSortNormal
With ActiveWorkbook.Worksheets("data").Sort
.SetRange Range("A1:B20")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

xlTrue is not valid it should be xlYes

 

[Dave Peterson]

Unqualified ranges will refer to the active sheet if the code is in a general
module.

Unqualified ranges will refer to the worksheet that holds the code if the code
is in a worksheet module.

And your _Click event looks like the code is in a worksheet module.

So qualify all your ranges:

Private Sub CommandButton1_Click()

Application.Goto Reference:="data"
ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("data").Sort.SortFields.Add _
Key:=ActiveWorkbook.Worksheets("data").Range( _
"A2:A20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("data").Sort
.SetRange ActiveWorkbook.Worksheets("data").Range("A1:B20")
.Header = xltrue
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

You may want to try it without the application.goto:
Private Sub CommandButton1_Click()

with ActiveWorkbook.Worksheets("data")

.Sort.SortFields.Clear
.Sort.SortFields.Add _
Key:=.Range("A2:A20"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

With .Sort
.SetRange .Range("A1:B20")
.Header = xltrue
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
end with

End Sub

(uncompiled, untested.)