A Sorting Problem

K

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
 
R

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
 
D

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VB Macro question 2
Automatic sort macro 2
range definition by cells numbers 3
Modify Sort Routine to inlcude All Data 2
Sorting Question 5
Sort by column, not range 1
Undo Macro Action 3
VBA 2 Codes 2

Top