Re: Sorting with empty line at the end

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

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
    ... Empty cell get sorted to the bottom of the list but empty strings get sorted ... Can you sort by the values in the original B, C, D & E? ... and the crowning place of kings ...
    (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 formulaically "empty" cells
    ... I'd use a helper column of cells with formulas like: ... Then sort by that helper column. ... > That way, if the divisor would be 0, it leaves the cell "empty." ... > the rows with "empty" percentages float to the top of the list. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Error Checking Code, Excel 2000 & 2003
    ... Sub ErrorCheckTEST() ... Dim cell as Range, cell1 as Range ... msg= "ERROR Description is empty" ... MsgBox "ERROR Description is empty" ...
    (microsoft.public.excel.programming)