Sorting Problem Need

[James]

Hello,

I have a Customer File record recorded on a sheet that spreads across
columns A thru M but the amount of rows a single record may take up can very
somewhat from say 2 rows to 6 rows its always different depending on the
size of the record

Coolum F contains a postal zip code that I would like to use to sort the
record by. Each record begins in column A with the same exact word 'Contact'
and I thought this would be a way to determine the start of a new record
which in turn would determine the length of the number of rows of the
previous record so to keep the complete record together during the sort.

The total numbers rows the code should cover are all or all rows with
information in them and I believe this is needed criteria. I hope I have
explained everything clearly without overstating what is needed.

Example:

Contact John Doe 234 hope st Ontario CA 91784 213
266-5487
Product F65A 2/5/05
SFO 3/1504

This sort of programming is way over my head so could someone please help if
this is even possible

 

[Charles Harmon]

James,
Excel has a sort feature. Have you tried it?

 

[Dave D-C]

This should be a good start.
It adds two columns for sorting.

Sub sub1()
Dim zRow&, zCol%, iRow&, tmpZip$
tmpZip = "00000"
zRow = UsedRange.SpecialCells(xlCellTypeLastCell).Row
zCol = UsedRange.SpecialCells(xlCellTypeLastCell).Column
For iRow = 1 To zRow
If Cells(iRow, 1) = "Contact" Then tmpZip = Cells(iRow, 6)
Cells(iRow, zCol + 1) = tmpZip
Cells(iRow, zCol + 2) = iRow
Next iRow
Range("a1", Cells(zRow, zCol + 2)).Sort _
key1:=Cells(1, zCol + 1), key2:=Cells(1, zCol + 2)
End Sub

 

[James]

Thanks for the help but having problem with the line yyou worte
zRow = UsedRange.SpecialCells(xlCellTypeLastCell).Row
seems it come back with a object required error
thanks
James

 

[James]

Hi

Yes but the built in sort feature will not allow for the variable I have in
each record with respect to the number of rows each record may have
James

 

[Dave D-C]

Hm. Works on my XL97
Try
zRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

 

[James]

Hi Dave,
Thanks that did the trick, appriciate the help
James