Try this code:

Sub List10()
Dim varr() As Variant
Dim i as long, j as long
Dim res as variant, res1 as variant
Dim cell as Range
ReDim varr(1 To 30)
i = 0
j = 0
For Each cell In Range("C10:L12")
j = j + 1
res = Application.Small(Range("C10:L12"), j)
If Not IsError(res) Then
If i = 0 Then
varr(1) = res
i = 1
Else
res1 = Application.Match(res, varr, 0)
If IsError(res1) Then
i = i + 1
varr(i) = res
End If
End If
End If
Next
Range("C15:L15").Value = varr
End Sub

--
Regards,
Tom Ogilvy

__
"Rick" wrote in message
...
3 16 1 3 2 2 empty etc
8 10 8 13 1 1 empty etc
4 2 4 1 4 14 empty etc

Above is the matrix C10 to L12 (k and L are not shown)

This is what I need extracted and entered in C15 to L15
2 3 4 8 10 13 14 16

The problem:-
I have a macro that extracts a number from the above
matrix and enters it in cell M6.
In the example the number is 1.
The matrix is C10:L12
Some of the cells do not contain data. (empty)
Some of the numbers are repeated.
Sometimes all cells are filled and there are no empties.

I'm trying to extract all the numbers in numeric order
EXCLUDING any that are repeated AND excluding the number
my macro inserts in M6. This number is always one of the
numbers in the matrix.
I wish to enter these numbers as above in numeric order in
cells C15 to L15.
I only need to extract 10 numbers, so any over this can be
dropped.

I have a macro to do this but it won't run on my version
of excel which is for Win95 ver7.

This is the code and I can't get past Dim cX as Collection.
Also Set cX = New Collection is in red.

Sub List_across_row()

Dim c As Range
Dim cX As Collection
Dim iCt As Integer

Set cX = New Collection
For Each c In Sheets("Sheet1").Range("C10:E12")
On Error Resume Next
cX.Add c.Value, CStr(c.Value)
Next c

For iCt = 1 To cX.Count
Sheets("Sheet1").Cells(15 + iCt, 3) = cX(iCt)
Next iCt

iCt = Sheets("Sheet1").Range("C16384").End
(xlUp).Row ' 65536
Set rng = Sheets("Sheet1").Range("C16:C" & iCt)
rng.Sort key1:=Range("C16"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
rng.Copy
Sheets("Sheet1").Range("C15").PasteSpecial Paste:=xlAll,
_
Operation:=xlNone, SkipBlanks:=False,
Transpose:=True
rng.Clear

End Sub

I don't know whether the rest of the macro runs correctly
as it has not run past the points mentioned above.
I suspect my version of Excel does not support the code.

Would anyone be able to help me get this code to run or
suggest some alternative code that gets around my
problem.

Thankyou in advance.

Regards,
Rick

Hello Tom,

Thanks for the assistance. Works perfectly but it includes
the number my macro inserts in cell M6. How do I exclude
that number (the cell contents) from the results produced
in C15 to L15. The code includes 1 in the results which in
the example was the number in M6.
Could you help please.

Cheers,
Rick

-----Original Message-----
Try this code:

Sub List10()
Dim varr() As Variant
Dim i as long, j as long
Dim res as variant, res1 as variant
Dim cell as Range
ReDim varr(1 To 30)
i = 0
j = 0
For Each cell In Range("C10:L12")
j = j + 1
res = Application.Small(Range("C10:L12"), j)
If Not IsError(res) Then
If i = 0 Then
varr(1) = res
i = 1
Else
res1 = Application.Match(res, varr, 0)
If IsError(res1) Then
i = i + 1
varr(i) = res
End If
End If
End If
Next
Range("C15:L15").Value = varr
End Sub

--
Regards,
Tom Ogilvy

__
"Rick" wrote in message
...
3 16 1 3 2 2 empty etc
8 10 8 13 1 1 empty etc
4 2 4 1 4 14 empty etc

Above is the matrix C10 to L12 (k and L are not shown)

This is what I need extracted and entered in C15 to L15
2 3 4 8 10 13 14 16

The problem:-
I have a macro that extracts a number from the above
matrix and enters it in cell M6.
In the example the number is 1.
The matrix is C10:L12
Some of the cells do not contain data. (empty)
Some of the numbers are repeated.
Sometimes all cells are filled and there are no empties.

I'm trying to extract all the numbers in numeric order
EXCLUDING any that are repeated AND excluding the number
my macro inserts in M6. This number is always one of the
numbers in the matrix.
I wish to enter these numbers as above in numeric order
in
cells C15 to L15.
I only need to extract 10 numbers, so any over this can
be
dropped.

I have a macro to do this but it won't run on my version
of excel which is for Win95 ver7.

This is the code and I can't get past Dim cX as
Collection.
Also Set cX = New Collection is in red.

Sub List_across_row()

Dim c As Range
Dim cX As Collection
Dim iCt As Integer

Set cX = New Collection
For Each c In Sheets("Sheet1").Range("C10:E12")
On Error Resume Next
cX.Add c.Value, CStr(c.Value)
Next c

For iCt = 1 To cX.Count
Sheets("Sheet1").Cells(15 + iCt, 3) = cX(iCt)
Next iCt

iCt = Sheets("Sheet1").Range("C16384").End
(xlUp).Row ' 65536
Set rng = Sheets("Sheet1").Range("C16:C" & iCt)
rng.Sort key1:=Range("C16"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
rng.Copy
Sheets("Sheet1").Range("C15").PasteSpecial
Paste:=xlAll,
_
Operation:=xlNone, SkipBlanks:=False,
Transpose:=True
rng.Clear

End Sub

I don't know whether the rest of the macro runs
correctly
as it has not run past the points mentioned above.
I suspect my version of Excel does not support the code.

Would anyone be able to help me get this code to run or
suggest some alternative code that gets around my
problem.

Thankyou in advance.

Regards,
Rick


.

Sorry - misunderstood how M6 played in the equation. I believe the below
will work.

Sub List10()
Dim varr() As Variant
Dim i as long, j as long
Dim res as variant, res1 as variant
Dim cell as Range
ReDim varr(1 To 30)
i = 0
j = 0
For Each cell In Range("C10:L12")
j = j + 1
res = Application.Small(Range("C10:L12"), j)
If Not IsError(res) Then
If i = 0 Then
If Range("M6").Value < res then
varr(1) = res
i = 1
End If
Else
res1 = Application.Match(res, varr, 0)
If IsError(res1) Then
if Range("M6").Value < res then
i = i + 1
varr(i) = res
End if
End If
End If
End If
Next
Range("C15:L15").Value = varr
End Sub


--
Regards,
Tom Ogilvy


"Rick" wrote in message
...
Hello Tom,

Thanks for the assistance. Works perfectly but it includes
the number my macro inserts in cell M6. How do I exclude
that number (the cell contents) from the results produced
in C15 to L15. The code includes 1 in the results which in
the example was the number in M6.
Could you help please.

Cheers,
Rick

-----Original Message-----
Try this code:

Sub List10()
Dim varr() As Variant
Dim i as long, j as long
Dim res as variant, res1 as variant
Dim cell as Range
ReDim varr(1 To 30)
i = 0
j = 0
For Each cell In Range("C10:L12")
j = j + 1
res = Application.Small(Range("C10:L12"), j)
If Not IsError(res) Then
If i = 0 Then
varr(1) = res
i = 1
Else
res1 = Application.Match(res, varr, 0)
If IsError(res1) Then
i = i + 1
varr(i) = res
End If
End If
End If
Next
Range("C15:L15").Value = varr
End Sub

--
Regards,
Tom Ogilvy

__
"Rick" wrote in message
...
3 16 1 3 2 2 empty etc
8 10 8 13 1 1 empty etc
4 2 4 1 4 14 empty etc

Above is the matrix C10 to L12 (k and L are not shown)

This is what I need extracted and entered in C15 to L15
2 3 4 8 10 13 14 16

The problem:-
I have a macro that extracts a number from the above
matrix and enters it in cell M6.
In the example the number is 1.
The matrix is C10:L12
Some of the cells do not contain data. (empty)
Some of the numbers are repeated.
Sometimes all cells are filled and there are no empties.

I'm trying to extract all the numbers in numeric order
EXCLUDING any that are repeated AND excluding the number
my macro inserts in M6. This number is always one of the
numbers in the matrix.
I wish to enter these numbers as above in numeric order
in
cells C15 to L15.
I only need to extract 10 numbers, so any over this can
be
dropped.

I have a macro to do this but it won't run on my version
of excel which is for Win95 ver7.

This is the code and I can't get past Dim cX as
Collection.
Also Set cX = New Collection is in red.

Sub List_across_row()

Dim c As Range
Dim cX As Collection
Dim iCt As Integer

Set cX = New Collection
For Each c In Sheets("Sheet1").Range("C10:E12")
On Error Resume Next
cX.Add c.Value, CStr(c.Value)
Next c

For iCt = 1 To cX.Count
Sheets("Sheet1").Cells(15 + iCt, 3) = cX(iCt)
Next iCt

iCt = Sheets("Sheet1").Range("C16384").End
(xlUp).Row ' 65536
Set rng = Sheets("Sheet1").Range("C16:C" & iCt)
rng.Sort key1:=Range("C16"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
rng.Copy
Sheets("Sheet1").Range("C15").PasteSpecial
Paste:=xlAll,
_
Operation:=xlNone, SkipBlanks:=False,
Transpose:=True
rng.Clear

End Sub

I don't know whether the rest of the macro runs
correctly
as it has not run past the points mentioned above.
I suspect my version of Excel does not support the code.

Would anyone be able to help me get this code to run or
suggest some alternative code that gets around my
problem.

Thankyou in advance.

Regards,
Rick


.

Perfect!
Thanks a lot, Tom.
Cheers,
Rick

-----Original Message-----
Sorry - misunderstood how M6 played in the equation. I
believe the below
will work.

Sub List10()
Dim varr() As Variant
Dim i as long, j as long
Dim res as variant, res1 as variant
Dim cell as Range
ReDim varr(1 To 30)
i = 0
j = 0
For Each cell In Range("C10:L12")
j = j + 1
res = Application.Small(Range("C10:L12"), j)
If Not IsError(res) Then
If i = 0 Then
If Range("M6").Value < res then
varr(1) = res
i = 1
End If
Else
res1 = Application.Match(res, varr, 0)
If IsError(res1) Then
if Range("M6").Value < res then
i = i + 1
varr(i) = res
End if
End If
End If
End If
Next
Range("C15:L15").Value = varr
End Sub


--
Regards,
Tom Ogilvy


"Rick" wrote in message
...
Hello Tom,

Thanks for the assistance. Works perfectly but it
includes
the number my macro inserts in cell M6. How do I exclude
that number (the cell contents) from the results
produced
in C15 to L15. The code includes 1 in the results which
in
the example was the number in M6.
Could you help please.

Cheers,
Rick

-----Original Message-----
Try this code:

Sub List10()
Dim varr() As Variant
Dim i as long, j as long
Dim res as variant, res1 as variant
Dim cell as Range
ReDim varr(1 To 30)
i = 0
j = 0
For Each cell In Range("C10:L12")
j = j + 1
res = Application.Small(Range("C10:L12"), j)
If Not IsError(res) Then
If i = 0 Then
varr(1) = res
i = 1
Else
res1 = Application.Match(res, varr, 0)
If IsError(res1) Then
i = i + 1
varr(i) = res
End If
End If
End If
Next
Range("C15:L15").Value = varr
End Sub

--
Regards,
Tom Ogilvy

__
"Rick" wrote in message
...
3 16 1 3 2 2 empty etc
8 10 8 13 1 1 empty etc
4 2 4 1 4 14 empty etc

Above is the matrix C10 to L12 (k and L are not
shown)

This is what I need extracted and entered in C15 to
L15
2 3 4 8 10 13 14 16

The problem:-
I have a macro that extracts a number from the above
matrix and enters it in cell M6.
In the example the number is 1.
The matrix is C10:L12
Some of the cells do not contain data. (empty)
Some of the numbers are repeated.
Sometimes all cells are filled and there are no
empties.

I'm trying to extract all the numbers in numeric
order
EXCLUDING any that are repeated AND excluding the
number
my macro inserts in M6. This number is always one of
the
numbers in the matrix.
I wish to enter these numbers as above in numeric
order
in
cells C15 to L15.
I only need to extract 10 numbers, so any over this
can
be
dropped.

I have a macro to do this but it won't run on my
version
of excel which is for Win95 ver7.

This is the code and I can't get past Dim cX as
Collection.
Also Set cX = New Collection is in red.

Sub List_across_row()

Dim c As Range
Dim cX As Collection
Dim iCt As Integer

Set cX = New Collection
For Each c In Sheets("Sheet1").Range("C10:E12")
On Error Resume Next
cX.Add c.Value, CStr(c.Value)
Next c

For iCt = 1 To cX.Count
Sheets("Sheet1").Cells(15 + iCt, 3) = cX(iCt)
Next iCt

iCt = Sheets("Sheet1").Range("C16384").End
(xlUp).Row ' 65536
Set rng = Sheets("Sheet1").Range("C16:C" & iCt)
rng.Sort key1:=Range("C16"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
rng.Copy
Sheets("Sheet1").Range("C15").PasteSpecial
Paste:=xlAll,
_
Operation:=xlNone, SkipBlanks:=False,
Transpose:=True
rng.Clear

End Sub

I don't know whether the rest of the macro runs
correctly
as it has not run past the points mentioned above.
I suspect my version of Excel does not support the
code.

Would anyone be able to help me get this code to run
or
suggest some alternative code that gets around my
problem.

Thankyou in advance.

Regards,
Rick


.



.