Sorting with empty line at the end

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
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?
 
Can you force the blank records to be zero length instead of physical
blanks? That'll work.
 
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!
 
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?
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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.
 
Hi,
How to find the first zero-length cell. I am not good in writing the code.
I tried Sandy's but didn't work! and posted the whole code.
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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!
 
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
 
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!!
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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????
 
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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
I'm Glad that you got is working

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top