I have a named range, Data1 (A1 to D5)

A B C D
1 ID Name Code Dept
2 100 Sam HIS Histrory
3 201 Kid FIN Registry
4 301 Tom FIN Unknown
5 101 Grace Unknown

1. The range is sorted for FIN in column C to appear before a blank cell.

2.I would like to sort this named range using a macro from when the cell in
column C start to equal to FIN, by column D.

I would like the code only for point 2.

let me reword your request to see if I understand it

You want the sort based on Column D for rows which have FIN in column C?
Just for the data in
A1:D5
I Assume A1:D1 are lables
there can be 1 to 4 rows with FIN in them
already adjacent
you want the range to be sorted in place.

Sub rangsrt()
nb = Application.WorksheetFunction.CountBlank(Range("C1 :C5"))
nf = Application.WorksheetFunction.CountIf(Range("C1:C5 "), "FIN")
sr = 6 - nb - nf
Range(Cells(sr, 1), Cells(sr + nf - 1, 4)).Select
Selection.Sort Key1:=Cells(sr, 4), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub



"nc" wrote:

I have a named range, Data1 (A1 to D5)

A B C D
1 ID Name Code Dept
2 100 Sam HIS Histrory
3 201 Kid FIN Registry
4 301 Tom FIN Unknown
5 101 Grace Unknown

1. The range is sorted for FIN in column C to appear before a blank cell.

2.I would like to sort this named range using a macro from when the cell in
column C start to equal to FIN, by column D.

I would like the code only for point 2.

I want the sort the range in two steps,

First sort the whole range on column D using a sortlist to ensure that FIN
is just before blank cells at the bottom of the list.

Second sort from where the first time FIN appear in coulmn based column D.

Thanks.


"bj" wrote:

let me reword your request to see if I understand it

You want the sort based on Column D for rows which have FIN in column C?
Just for the data in
A1:D5
I Assume A1:D1 are lables
there can be 1 to 4 rows with FIN in them
already adjacent
you want the range to be sorted in place.

Sub rangsrt()
nb = Application.WorksheetFunction.CountBlank(Range("C1 :C5"))
nf = Application.WorksheetFunction.CountIf(Range("C1:C5 "), "FIN")
sr = 6 - nb - nf
Range(Cells(sr, 1), Cells(sr + nf - 1, 4)).Select
Selection.Sort Key1:=Cells(sr, 4), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub



"nc" wrote:

I have a named range, Data1 (A1 to D5)

A B C D
1 ID Name Code Dept
2 100 Sam HIS Histrory
3 201 Kid FIN Registry
4 301 Tom FIN Unknown
5 101 Grace Unknown

1. The range is sorted for FIN in column C to appear before a blank cell.

2.I would like to sort this named range using a macro from when the cell in
column C start to equal to FIN, by column D.

I would like the code only for point 2.

Will there always be a blank cell in Column C?
Will Any FINs always be together?
could the initial position of the blank be above a FIN?
In other words I still am not sure I know what you want.
Could you put in a couple more examples of starting and ending data sets
shows extremes of intial start data and how you want them to end


"nc" wrote:

I want the sort the range in two steps,

First sort the whole range on column D using a sortlist to ensure that FIN
is just before blank cells at the bottom of the list.

Second sort from where the first time FIN appear in coulmn based column D.

Thanks.


"bj" wrote:

let me reword your request to see if I understand it

You want the sort based on Column D for rows which have FIN in column C?
Just for the data in
A1:D5
I Assume A1:D1 are lables
there can be 1 to 4 rows with FIN in them
already adjacent
you want the range to be sorted in place.

Sub rangsrt()
nb = Application.WorksheetFunction.CountBlank(Range("C1 :C5"))
nf = Application.WorksheetFunction.CountIf(Range("C1:C5 "), "FIN")
sr = 6 - nb - nf
Range(Cells(sr, 1), Cells(sr + nf - 1, 4)).Select
Selection.Sort Key1:=Cells(sr, 4), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub



"nc" wrote:

I have a named range, Data1 (A1 to D5)

A B C D
1 ID Name Code Dept
2 100 Sam HIS Histrory
3 201 Kid FIN Registry
4 301 Tom FIN Unknown
5 101 Grace Unknown

1. The range is sorted for FIN in column C to appear before a blank cell.

2.I would like to sort this named range using a macro from when the cell in
column C start to equal to FIN, by column D.

I would like the code only for point 2.

Hi bj

Thanks for your reply.

Will there always be a blank cell in Column C?
Yes. A few.

Will Any FINs always be together?
Yes. If they exist. The sort should start from where FIN first appear,
if non, then from first blank cell.

could the initial position of the blank be above a FIN?
No. (The list would have already been sorted on column C for FIN to
appear above blank.)

E.g.

A B C D
1 ID Name Code Dept
2 100 Sam ANT Histrory
3 102 Franco HIS Anthropology
4 201 Kid FIN Registry
5 301 Tom FIN Unknown
6 101 Grace Registry


After the sort routine, row 6 has moved up to row 5, the list was sorted on
column D from where FIN first appears in column C (row 4). If FIN did not
exist the sort will start from where a blank cell first appear in column C.

A B C D
1 ID Name Code Dept
2 100 Sam ANT Histrory
3 102 Franco HIS Anthropology
4 201 Kid FIN Registry
5 101 Grace Registry
6 301 Tom FIN Unknown


"bj" wrote:

Will there always be a blank cell in Column C?
Will Any FINs always be together?
could the initial position of the blank be above a FIN?
In other words I still am not sure I know what you want.
Could you put in a couple more examples of starting and ending data sets
shows extremes of intial start data and how you want them to end


"nc" wrote:

I want the sort the range in two steps,

First sort the whole range on column D using a sortlist to ensure that FIN
is just before blank cells at the bottom of the list.

Second sort from where the first time FIN appear in coulmn based column D.

Thanks.


"bj" wrote:

let me reword your request to see if I understand it

You want the sort based on Column D for rows which have FIN in column C?
Just for the data in
A1:D5
I Assume A1:D1 are lables
there can be 1 to 4 rows with FIN in them
already adjacent
you want the range to be sorted in place.

Sub rangsrt()
nb = Application.WorksheetFunction.CountBlank(Range("C1 :C5"))
nf = Application.WorksheetFunction.CountIf(Range("C1:C5 "), "FIN")
sr = 6 - nb - nf
Range(Cells(sr, 1), Cells(sr + nf - 1, 4)).Select
Selection.Sort Key1:=Cells(sr, 4), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub



"nc" wrote:

I have a named range, Data1 (A1 to D5)

A B C D
1 ID Name Code Dept
2 100 Sam HIS Histrory
3 201 Kid FIN Registry
4 301 Tom FIN Unknown
5 101 Grace Unknown

1. The range is sorted for FIN in column C to appear before a blank cell.

2.I would like to sort this named range using a macro from when the cell in
column C start to equal to FIN, by column D.

I would like the code only for point 2.

I notice this data set is A1:D6

if there is a way to determine how many rows there will be, or if any
columns will only have data from this named data range, rs could be
calculated rather than just entered in the macro.


Sub rangsrt()
rs = 6
nb = Application.WorksheetFunction.CountBlank(Range("C1 :C"&rs))
nf = Application.WorksheetFunction.CountIf(Range("C1:C" &rs), "FIN")
sr = rs+1 - nb - nf
Range(Cells(sr, 1), Cells(rs, 4)).Select
Selection.Sort Key1:=Cells(sr, 4), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

"nc" wrote:

Hi bj

Thanks for your reply.

Will there always be a blank cell in Column C?
Yes. A few.

Will Any FINs always be together?
Yes. If they exist. The sort should start from where FIN first appear,
if non, then from first blank cell.

could the initial position of the blank be above a FIN?
No. (The list would have already been sorted on column C for FIN to
appear above blank.)

E.g.

A B C D
1 ID Name Code Dept
2 100 Sam ANT Histrory
3 102 Franco HIS Anthropology
4 201 Kid FIN Registry
5 301 Tom FIN Unknown
6 101 Grace Registry


After the sort routine, row 6 has moved up to row 5, the list was sorted on
column D from where FIN first appears in column C (row 4). If FIN did not
exist the sort will start from where a blank cell first appear in column C.

A B C D
1 ID Name Code Dept
2 100 Sam ANT Histrory
3 102 Franco HIS Anthropology
4 201 Kid FIN Registry
5 101 Grace Registry
6 301 Tom FIN Unknown


"bj" wrote:

Will there always be a blank cell in Column C?
Will Any FINs always be together?
could the initial position of the blank be above a FIN?
In other words I still am not sure I know what you want.
Could you put in a couple more examples of starting and ending data sets
shows extremes of intial start data and how you want them to end


"nc" wrote:

I want the sort the range in two steps,

First sort the whole range on column D using a sortlist to ensure that FIN
is just before blank cells at the bottom of the list.

Second sort from where the first time FIN appear in coulmn based column D.

Thanks.


"bj" wrote:

let me reword your request to see if I understand it

You want the sort based on Column D for rows which have FIN in column C?
Just for the data in
A1:D5
I Assume A1:D1 are lables
there can be 1 to 4 rows with FIN in them
already adjacent
you want the range to be sorted in place.

Sub rangsrt()
nb = Application.WorksheetFunction.CountBlank(Range("C1 :C5"))
nf = Application.WorksheetFunction.CountIf(Range("C1:C5 "), "FIN")
sr = 6 - nb - nf
Range(Cells(sr, 1), Cells(sr + nf - 1, 4)).Select
Selection.Sort Key1:=Cells(sr, 4), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub



"nc" wrote:

I have a named range, Data1 (A1 to D5)

A B C D
1 ID Name Code Dept
2 100 Sam HIS Histrory
3 201 Kid FIN Registry
4 301 Tom FIN Unknown
5 101 Grace Unknown

1. The range is sorted for FIN in column C to appear before a blank cell.

2.I would like to sort this named range using a macro from when the cell in
column C start to equal to FIN, by column D.

I would like the code only for point 2.