Sorting with empty line at the end

K

Khalil Handal

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?
 
W

Wild Bill

Can you force the blank records to be zero length instead of physical
blanks? That'll work.
 
K

Khalil Handal

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!
 
W

Wild Bill

You can't clear the blank name cells? Because if you do, and then sort,
the omitted names will be at the end, and you can deduce the row of the
last supplied name with the worksheet function COUNTA and using that
offset (minus 1) from column 14. Or use End(xldown). Or just use code to
find the first zero-length cell. All of those will suffer if you have
physical blanks.

If you really must retain the blanks, one approach would be to do as
above and then restore them to blanks. Another would be to change them
to the string zzzzzz and restore them to blanks after sorting, but you
won't be as easily able to locate the row of the last supplied name.

Unfortunately you'll still need the 58 (or better yet, use a named
range) to tell to the sort. You can use the smaller row count (with
supplied names) for later operations, but you want it sorted first,
right?
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
K

Khalil Handal

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.
 
S

Sandy Mann

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.
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
K

Khalil Handal

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!
 
P

Pete_UK

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
 
K

Khalil Handal

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!!
 
S

Sandy Mann

Sounds like Pete has nailed it.

What is happening now with the sort?

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
K

Khalil Handal

Hi,
By saying "First of all sort by Column E then by B, C & D in that order" you
mean to do TWO time sorting (this is what I understood) since in: Data|sort
we have only three options!!!!!
I tried it with them and it seems to work fine for the selected range.
I have the following VBA code that worked for columns B, C, D.
What Adjustment needs to be done to sort column E at first????
 
K

Khalil Handal

Forgot to paste the code:
With ActiveSheet
.Unprotect Password:="1230"
' Mann Start
.Range("B14:FQ58").Select
Selection.Sort Key1:=Range("B14"), Order1:=xlAscending,
Key2:=Range("C14" _
), Order2:=xlAscending, Key3:=Range("D14"), Order3:=xlAscending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
.Range("B14").Select
.Protect Password:="1230"
End With
 
S

Sandy Mann

Yes just sort it twice. Here is the sort code that I got when I recorded
it:

Selection.Sort Key1:=Range("E14"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, _
Key2:=Range("C14" ), Order2:=xlAscending, _
Key3:=Range("D14"), Order3:=xlAscending, _
Header :=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom

Remember that you don't have to select the range if you use With/End With

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
K

Khalil Handal

Hi,
Thanks it worked fine.

Sandy Mann said:
Yes just sort it twice. Here is the sort code that I got when I recorded
it:

Selection.Sort Key1:=Range("E14"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, _
Key2:=Range("C14" ), Order2:=xlAscending, _
Key3:=Range("D14"), Order3:=xlAscending, _
Header :=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom

Remember that you don't have to select the range if you use With/End With

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
S

Sandy Mann

I'm Glad that you got is working

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top