Data sorting with a twist
 

Not sure how to ask this, but will do my best. I have the code below, which
places the contents of ListBox1 into the active cell (I use this to fill in
and edit an annual vacation calendar for a department of about 60 people).
Each day there can be up to 5 people off, so each day is made up of 5 rows.
When entering or removing names (and this is do through out the year) I
click on the first empty cell (or the cell containing the name I want to
delete), What I need is some code that will some how select all 5 rows and
sort the names.




Private Sub OK_Click()

Application.ScreenUpdating = False
On Error GoTo EndMacro
Selection = ListBox1.Value
EndMacro:
Me.Hide
Unload EmployeeList
ListBox1.ListIndex = -1
Application.ScreenUpdating = True

End Sub

Data sorting with a twist
 

If you want to delete the current row and 4 below it

Selection.Resize(4).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote in message
...
Not sure how to ask this, but will do my best. I have the code below,
which
places the contents of ListBox1 into the active cell (I use this to fill
in
and edit an annual vacation calendar for a department of about 60 people).
Each day there can be up to 5 people off, so each day is made up of 5
rows.
When entering or removing names (and this is do through out the year) I
click on the first empty cell (or the cell containing the name I want to
delete), What I need is some code that will some how select all 5 rows and
sort the names.




Private Sub OK_Click()

Application.ScreenUpdating = False
On Error GoTo EndMacro
Selection = ListBox1.Value
EndMacro:
Me.Hide
Unload EmployeeList
ListBox1.ListIndex = -1
Application.ScreenUpdating = True

End Sub

Data sorting with a twist
 

Not sure that reply was for my question, I am looking to sort the rows.


"Tom Ogilvy" wrote in message
...
If you want to delete the current row and 4 below it

Selection.Resize(4).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote in message
...
Not sure how to ask this, but will do my best. I have the code below,
which
places the contents of ListBox1 into the active cell (I use this to fill
in
and edit an annual vacation calendar for a department of about 60
people).
Each day there can be up to 5 people off, so each day is made up of 5
rows.
When entering or removing names (and this is do through out the year) I
click on the first empty cell (or the cell containing the name I want to
delete), What I need is some code that will some how select all 5 rows
and
sort the names.




Private Sub OK_Click()

Application.ScreenUpdating = False
On Error GoTo EndMacro
Selection = ListBox1.Value
EndMacro:
Me.Hide
Unload EmployeeList
ListBox1.ListIndex = -1
Application.ScreenUpdating = True

End Sub

Data sorting with a twist
 

Patrick,

I know this sounds like a get out but you could just record the macro.

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=476546

Data sorting with a twist
 

I click on the first empty cell (or the cell containing the name I want
to
**delete**)

ActiveCell.Resize(5).Sort Key1:=ActiveCell Order:=xlAscending

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote in message
...
Not sure that reply was for my question, I am looking to sort the rows.


"Tom Ogilvy" wrote in message
...
If you want to delete the current row and 4 below it

Selection.Resize(4).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote in message
...
Not sure how to ask this, but will do my best. I have the code below,
which
places the contents of ListBox1 into the active cell (I use this to
fill
in
and edit an annual vacation calendar for a department of about 60
people).
Each day there can be up to 5 people off, so each day is made up of 5
rows.
When entering or removing names (and this is do through out the year) I
click on the first empty cell (or the cell containing the name I want
to
delete), What I need is some code that will some how select all 5 rows
and
sort the names.




Private Sub OK_Click()

Application.ScreenUpdating = False
On Error GoTo EndMacro
Selection = ListBox1.Value
EndMacro:
Me.Hide
Unload EmployeeList
ListBox1.ListIndex = -1
Application.ScreenUpdating = True

End Sub

Data sorting with a twist
 

oops

ActiveCell.Resize(5).EntireRow _
.Sort Key1:=ActiveCell Order:=xlAscending

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I click on the first empty cell (or the cell containing the name I want
to
**delete**)

ActiveCell.Resize(5).Sort Key1:=ActiveCell Order:=xlAscending

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote in message
...
Not sure that reply was for my question, I am looking to sort the rows.


"Tom Ogilvy" wrote in message
...
If you want to delete the current row and 4 below it

Selection.Resize(4).EntireRow.Delete

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote in message
...
Not sure how to ask this, but will do my best. I have the code below,
which
places the contents of ListBox1 into the active cell (I use this to
fill
in
and edit an annual vacation calendar for a department of about 60
people).
Each day there can be up to 5 people off, so each day is made up of 5
rows.
When entering or removing names (and this is do through out the year)
I
click on the first empty cell (or the cell containing the name I want
to
delete), What I need is some code that will some how select all 5
rows
and
sort the names.




Private Sub OK_Click()

Application.ScreenUpdating = False
On Error GoTo EndMacro
Selection = ListBox1.Value
EndMacro:
Me.Hide
Unload EmployeeList
ListBox1.ListIndex = -1
Application.ScreenUpdating = True

End Sub