From: Jack Sons on
PM,

I did like you advised, and now it works like a charm. Marvelous, thank you.

Jack.


"Project Mangler" <dblack(a)ntlworld.com> schreef in bericht
news:OHfrrQ1ALHA.348(a)TK2MSFTNGP06.phx.gbl...
> Hi Jack,
>
> Apologies, dumb error on my part:
>
> I assume that your populated range begins in K1?
>
> If so change Set A = Cells(1, 11).End(xlDown)
> to
> Set A = Range("K1")
>
> HTH
>
>
> "Project Mangler" <dblack(a)ntlworld.com> wrote in message
> news:eE8ZKE1ALHA.1892(a)TK2MSFTNGP05.phx.gbl...
>> Hi Jack,
>>
>>
>> "Jack Sons" <sonsindenhaag(a)gmail.com> wrote in message
>> news:6B37517B-7353-4400-94B6-0A692CCEBC1E(a)microsoft.com...
>> > PM,
>> >
>> > Thanks for the code. I tried it but it does nothing.
>>
>> Well that is disappointing.
>>
>> >As far as my abilities
>> > go I checked the working of the code. I saw that it starts wit setting
>> > A
>> and
>> > B both to equal the content of the last cell in column K.
>>
>> I'm not clear why
>> Set A = Cells(1, 11).End(xlDown)
>> would ever find the last cell in an occupied column unless that column
> only
>> had one populated cell. If that is what is happening then the rest of the
>> code will never work.
>>
>> You could substitute the line above with
>> Set A = Range("K1").End(xlDown)
>> but really it shouldn't make any difference.
>>
>> > I think the code misses something.
>>
>> All I can tell you is that it works here in excel 2003. I don't post
>> untested code but perhaps I misread your original post.
>> My test sheet is populated from A3 to AV22 with a mix of numbers and text
> in
>> alternate columns (1 - 22) for numbers, (a - t) for text with one number
> or
>> letter per cell in ascending order down the column.
>> K8 - K13 have blue font (colorindex 5), all others are black font.
>>
>> The code correctly identifies the start and end of the occupied range in
>> column K and sorts A8 - AV13 in descending order. The biggest flaw in the
>> code that I have identified is that it will only sort the first range of
>> coloured cells encountered should there be more than one block of them.
>>
>> > For your information: in the case that I checked the first 9 cells (1
>> header
>> > and 8 data) are all with black font (font code 1). Then follow 13 cells
>> with
>> > font code 5 and after that 3 cells with again black font.
>>
>> I assume that this is in Column K as indicated in your original post.
>>
>> > Please be so kind as to make the necessary changes in the code (I can't
>> > figure them out) and - if it is not to time consuming - please explain
> how
>> > the code works. A thousand thanks in advance.
>>
>> I suggest that you try a repost in microsoft.public.excel.programming.
> Thre
>> are much more capable coders in there than me. Perhaps one of them will
>> correct my attempt.
>>
>> Good luck!
>>
>>
>
>