Simple Sort question

  • Thread starter Thread starter TBA
  • Start date Start date
T

TBA

Excel 97
Windows 2k Pro

I know that using Excel's built in Sort menu option that I can only sort up
to three columns at a time. I went ahead and recorded a sort as a macro to
get the basic code for doing this in VBA, so I was wondering if I can add a
4th, 5th or 6th sort key in the VBA code for the Macro. I've tried doing
this by mimicking the code but adding a 4th key, using the same syntax as
the macro code, but I got an "unknown object" error. Is it just not meant
to be?

-gk-
 
You can record the steps as you perform multiple sorts. For example, you
may want to sort by Country, Region, City, LastName and FirstName.

LastName and FirstName are the least important fields in the sorting
process, so they can be sorted first. (Data>Sort, By LastName, then by
FirstName)

Then sort the remaining fields (By Country, then by Region, then by
City). Excel will retain as much as possible from the earlier sorts.
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
nest the ArrayRowFilter functions:

=ArrayRowFilter2(ArrayRowFilter1(ArrayRowFilter1(ArrayRowFilter1(ArrayRowFilter1(ArrayRowFilter1(datarange,1stCol,1stCrit),2ndCol,2ndCrit),3rdCol,3rdCrit),4thCol,4thCrit),5thCol,5thCrit),6thCol,6thCrit)
array entered into a range sufficiently large to accommodate the final
output. Note that the first function is ArrayRowFilter2, the remainder
are ArrayRowFilter1. This is to avoid getting an error message if the
selected range is large enough for the final output but not large enough
for an intermediate output. If the selected range is not large enough
for the final output, the error message will indicate the range size to
be selected.

Alan Beban
 
Back
Top