sorting

[Jack Sons]

Hi all,

In my sheet "tijdpad" in column K could be cells of which the content
ends with the word "vonnis". The number of such cells could be
anything between zero and, say, 20. If these cells occur, they always
occur in a consecutive range. But I don't know where that range starts
and ends, it could be anywhere except starting in row 1.

I need that range (from colum A up to column AZ) to be sorted with the
corresponding cells in column S as key (ascending). If I would write
the necessary code I would find the start and end of the range by
using do loops, which certainly is not a clever or efficient way.
Please advice me what code to use, your help will be appreciated very
much.

I use XP SP2 and Excel 2k.

 

[Dave Peterson]

This seemed to work ok for me:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range
Dim StrToFind As String

Set wks = Worksheets("tijdpad")

StrToFind = "vonnis"

With wks
With .Range("K:K")
Set TopCell = .Cells.Find(what:=StrToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

Set BotCell = .Cells.Find(what:=StrToFind, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)

If TopCell Is Nothing _
Or BotCell Is Nothing Then
MsgBox "Not found!"
Exit Sub
End If

If TopCell.Row = BotCell.Row Then
MsgBox "Only one row!"
Exit Sub
End If

End With

With .Range(.Cells(TopCell.Row, "A"), .Cells(BotCell.Row, "AZ"))
.Cells.Sort _
key1:=.Columns(19), _
order1:=xlAscending, _
Header:=xlNo, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End With

End Sub

 

[Jack Sons]

This seemed to work ok for me:

Option Explicit
Sub testme()
     Dim wks As Worksheet
     Dim TopCell As Range
     Dim BotCell As Range
     Dim StrToFind As String

     Set wks = Worksheets("tijdpad")

     StrToFind = "vonnis"

     With wks
         With .Range("K:K")
             Set TopCell = .Cells.Find(what:=StrToFind,_
                                 after:=.Cells(.Cells.Count), _
                                 LookIn:=xlValues, _
                                 lookat:=xlPart, _
                                 searchorder:=xlByRows, _
                                 searchdirection:=xlNext, _
                                 MatchCase:=False)

             Set BotCell = .Cells.Find(what:=StrToFind,_
                                 after:=.Cells(1), _
                                 LookIn:=xlValues, _
                                 lookat:=xlPart, _
                                 searchorder:=xlByRows, _
                                 searchdirection:=xlPrevious, _
                                 MatchCase:=False)

             If TopCell Is Nothing _
              Or BotCell Is Nothing Then
                 MsgBox "Not found!"
                 Exit Sub
             End If

             If TopCell.Row = BotCell.Row Then
                 MsgBox "Only one row!"
                 Exit Sub
             End If

         End With

         With .Range(.Cells(TopCell.Row, "A"), .Cells(BotCell.Row, "AZ"))
             .Cells.Sort _
                 key1:=.Columns(19), _
                 order1:=xlAscending, _
                 Header:=xlNo, _
                 MatchCase:=False, _
                 Orientation:=xlTopToBottom
         End With

     End With

End Sub

Dave,

As always: fast as lightning and your code works like a charm. And
again I learned something. Thank you!

Question:
I posted in http://groups.google.com/group/microsoft.public.excel.misc/
and it worked, but I thought that Microsoft had already discontinued
this NG.
Please shed some light on this.

Jack.

 

[James Ravenswood]

Dave,

As always: fast as lightning and your code works like a charm. And
again I learned something. Thank you!

Question:
I posted inhttp://groups.google.com/group/microsoft.public.excel.misc/
and it worked, but I thought that Microsoft had already discontinued
this NG.
Please shed some light on this.

Jack.- Hide quoted text -

- Show quoted text -

The Google interface is tilll working. Questions can still be asked
and answered.
Old posts can still be searched. The new Microsoft area is:

http://social.answers.microsoft.com/Forums/en-US/officeprog/threads

 

[Dave Peterson]

Just to add to James' response.

MS discontinued their news servers (some???, but soon to be all). But the NNTP
stuff is carried by lots of other news servers.

The question that remains is if or when MS sends out a please remove the
microsoft.public.* newsgroups, how many of these independent news servers will
respect this request and drop those newsgroups.

I've signed up for 3 free (I'm cheap) newsservers.
news.aioe.org (some limits, like: 25 posts per day))
news.eternal-september.org (http://www.eternal-september.org to read about it)
news.albasani.net

I know I had to register with the bottom two. I don't remember with aioe.org
(it's been a long time).

And I'm using the eternal-september.org news server. Traffic is down
considerably in the excel newsgroups. I'm not sure it'll ever grow.

But the community bridge by Jochen Kalmbach works pretty darn nice!
Ron de Bruin has some notes here:
http://www.rondebruin.nl/nntpbridge.htm

If you can use a newsreader (are you blocked at work???), you may not notice too
much of a difference!