Re: Sorting with empty line at the end

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Empty cell get sorted to the bottom of the list but empty strings get sorted
to the top. Can you sort by the values in the original B, C, D & E?

First of all sort by Column E then by B, C & D in that order

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@xxxxxxxxxxxxxx
Replace @mailinator.com with @tiscali.co.uk


"Khalil Handal" <khhandal@xxxxxxxxxx> wrote in message
news:%2394ki1$4HHA.1992@xxxxxxxxxxxxxxxxxxxxxxx
Hi Pete<
Tried the TRIM you suggested.
The code is:
Private Sub CommandButton5_Click()
'Sorting Names aphabatically depending on column F
With ActiveSheet
.Unprotect Password:="1230"
' Mann Start

EndRow = Range("F14").End(xlDown).Row

With Range("B14:FQ" & EndRow)
.Sort Key1:=Range("F14"), Order1:=xlAscending
.Range("B14").Select
End With

.Protect Password:="1230"
End With

End Sub

Still didn't work and has empty line (Not filled yet) being first lines.
Note:
=Code(F14) gave: #VALUE error
=Len(F14) gave: 0

Any other suggestions!!

"Pete_UK" <pashurst@xxxxxxxxxxx> wrote in message
news:1187703897.845652.265080@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
The cell F14 is not going to be empty - it will contain 3 spaces, even
if cells B14:E14 are all empty. You should change your formula in F14
to:

=TRIM(B14&" "&C14&" "&D14&" "&E14)

then copy this down and re-do your sort.

Hope this helps.

Pete

On Aug 21, 2:03 pm, "Khalil Handal" <khhan...@xxxxxxxxxx> wrote:
Hi,
I will chekc what you mentioned.
I don't know if this is of any significance or not! Data in cells F are
combinations of what is entered in cells B, C, D, E!!! (F14==B14&"
"&C14&"
"&D14&" "&E14)

Note:
=LEN(F43) gave the value of 3
=code(F43) gave the value of 32
Now it is clear to me what you are talking about.
Any sugestions!

"Sandy Mann" <sandyma...@xxxxxxxxxxxxxx> wrote in message

news:%23Ge7sX%234HHA.4712@xxxxxxxxxxxxxxxxxxxxxxx



I still have the empty lines at the begining

Do you have cells that have been *cleared* by someone entering a
space?

In a spare cell try the formula

=LEN(F14)

which should be 0 if there is nothing in the cell

or try:

=CODE(F14)

if it returns 32 you have a space if 160 you have a non-breaking space

i.e. If line 40 has the last name then the sort will be from14 to 40
and if line 50 has the last name in cel F50 then the sort will be
from 14
to 50.

The code should do that if you have continuous data. If you have gaps
then
use:

EndRow = Cells(Rows.Count, 6).End(xlUp).Row + 1

in place of the original line. If you have, (or could have), other
data
in
column F below you sort range then replace the Rows.Count with the
number
of
the last cell before the additional data. ie if you have something in
F100
that you don't want included in the sort then change the line to:

EndRow = Cells(99, 6).End(xlUp).Row + 1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandyma...@xxxxxxxxxxxxxx
Replace @mailinator.com with @tiscali.co.uk

"Khalil Handal" <khhan...@xxxxxxxxxx> wrote in message
news:eNlvTv94HHA.6024@xxxxxxxxxxxxxxxxxxxxxxx
Hi,
I tried what you wrote and I have all my code below:

Private Sub CommandButton5_Click()
With ActiveSheet
.Unprotect Password:="1230"
EndRow = Range("F14").End(xlDown).Row
With Range("B14:FQ" & EndRow)
.Sort Key1:=Range("F14"), Order1:=xlAscending
.Range("B14").Select
End With
.Protect Password:="1230"
End With
End Sub

I still have the empty lines at the begining.

I am not sure if I am using the right words:
I want Excel to take only the line that contains data in colomn F.
i.e. If line 40 has the last name then the sort will be from14 to 40
and if line 50 has the last name in cel F50 then the sort will be
from 14
to 50.

"Sandy Mann" <sandyma...@xxxxxxxxxxxxxx> wrote in message
news:OrrEIw84HHA.2752@xxxxxxxxxxxxxxxxxxxxxxx
You can change you code to:

EndRow = Range("F14").End(xlDown).Row
Range("B14:FQ" & EndRow).Select
Selection.Sort Key1:=Range("F14"), Order1:=xlAscending
.Range("B14").Select

the period before the Range("B14").Select caused me a problem. You
can
also get rid of the Selects by using with:

EndRow = Range("F14").End(xlDown).Row

With Range("B14:FQ" & EndRow)
.Sort Key1:=Range("F14"), Order1:=xlAscending
.Range("B14").Select
End With

Ironicall you then NEED the period before the Range("B14").Select.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandyma...@xxxxxxxxxxxxxx
Replace @mailinator.com with @tiscali.co.uk

"Khalil Handal" <khhan...@xxxxxxxxxx> wrote in message
news:OhNu0s74HHA.1212@xxxxxxxxxxxxxxxxxxxxxxx
Hi,
No.
Later on, I might add more records.
Refrasing the idea is (in other words):
To find first empty row in colomn F and use the number of that row
instead of the 58 in the range B14:FQ58.
I am not sure if it can be done this way!

"Wild Bill" <DecapitateSpamm...@xxxxxxxxxx> wrote in message
news:46ce7f9a.224490312@xxxxxxxxxxxxxxxxxxxxxxxx
Can you force the blank records to be zero length instead of
physical
blanks? That'll work.

On Tue, 21 Aug 2007 05:44:43 +0300, "Khalil Handal"
<khhan...@xxxxxxxxxx> wrote:

Hi,
I have recorded this macro to sort a list of names up to 45 names.
Not
all
the names are written.

Private Sub CommandButton5_Click()

Range("B14:FQ58").Select
Selection.Sort Key1:=Range("F14"), Order1:=xlAscending
.Range("B14").Select

End Sub

When I run the macro all the EMPTY LINES are at the begining.
What changes should be done to the code so as to keep the empty
line
be
at
the END and still have the assending order for the list?- Hide
quoted text -

- Show quoted text -







.

Relevant Pages

  • Re: Sorting with empty line at the end
    ... The cell F14 is not going to be empty - it will contain 3 spaces, ... then copy this down and re-do your sort. ...
    (microsoft.public.excel)
  • Re: Sorting with empty line at the end
    ... In a spare cell try the formula ... and if line 50 has the last name in cel F50 then the sort will be from 14 ... EndRow = Range.End.Row ... I still have the empty lines at the begining. ...
    (microsoft.public.excel)
  • Re: Sorting with empty line at the end
    ... In a spare cell try the formula ... and if line 50 has the last name in cel F50 then the sort will be from 14 ... EndRow = Range.End.Row ... I still have the empty lines at the begining. ...
    (microsoft.public.excel)
  • Re: Sorting with empty line at the end
    ... What is happening now with the sort? ... and the crowning place of kings ... In a spare cell try the formula ...
    (microsoft.public.excel)
  • Re: The Rule of Universal Generalization
    ... Then the sort assumptions from and ... the sorts can be empty, ... function symbols, besides the non-empty axiom. ...
    (sci.logic)