help me on rearrange cells based on its numerical values without repeating any number

[amrezzat]

rearrange cells based on its numerical values without repeating

--------------------------------------------------------------------------------

with this sub

Sub doit()
Range("M1").CurrentRegion.ClearContents
Range("A1").Resize(10, 2).Copy Destination:=Range("M1")
Range("M1").CurrentRegion.Sort Key1:=Range("M1"), Order1:=xlAscending
End Sub


it works
but i dont want to repeat the numbers
i mean
if i input
1 microsoft
2 intel
1 microsoft
the results will be
1
1
2


i dont want that
i want only
1
2
no repeating
how can i do that?
noting that the input my be so long may be 1000 rows
but they are only 50 companies (from 1 to 50)
so the results will be in a max 50 rows

and i want that code to activate(rerun) each time i enter the sheet2

how can i do that ??

[L. Howard Kittle]

This is some code I found at a Chip Pearson site dealing with duplicates. I
modified it to suit another purpose which is similar to what you are doing.

It sorts the data in column A to make sure all the 1's 2's etc are together
and then removes all but one and then sorts again to remove the blanks.

Perhaps try on some test data and of course change the cell address to suit
your data.

Sub FixDuplicateRows()
Dim RowNdx As Long
Dim ColNum As Integer
Dim LastRow As Long
Dim DataRng As Range
Dim i As Integer

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DataRng = Range("A2:A" & LastRow)
DataRng.Select

Application.ScreenUpdating = False

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

' This is Chip's original code with this tiny mod
' Cells(RowNdx, ColNum).Value = "----"
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Value = ""
End If
Next RowNdx
' End of Chip's code

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


Range("A2").Select
Application.ScreenUpdating = True
End Sub

HTH
Regards,
Howard

"amrezzat" <[email protected]> wrote in
message news:[email protected]...
>
> rearrange cells based on its numerical values without repeating
>
> --------------------------------------------------------------------------------
>
> with this sub
>
> Sub doit()
> Range("M1").CurrentRegion.ClearContents
> Range("A1").Resize(10, 2).Copy Destination:=Range("M1")
> Range("M1").CurrentRegion.Sort Key1:=Range("M1"), Order1:=xlAscending
> End Sub
>
>
> it works
> but i dont want to repeat the numbers
> i mean
> if i input
> 1 microsoft
> 2 intel
> 1 microsoft
> the results will be
> 1
> 1
> 2
>
>
> i dont want that
> i want only
> 1
> 2
> no repeating
> how can i do that?
> noting that the input my be so long may be 1000 rows
> but they are only 50 companies (from 1 to 50)
> so the results will be in a max 50 rows
>
> and i want that code to activate(rerun) each time i enter the sheet2
>
> how can i do that ??
>
>
> --
> amrezzat
> ------------------------------------------------------------------------
> amrezzat's Profile:
> http://www.excelforum.com/member.php...o&userid=28766
> View this thread: http://www.excelforum.com/showthread...hreadid=487039
>

[amrezzat]

it works on an empty sheet
but on my original sheet
an error message "application-defined or object-defined error"
and all values in and formuals in the sheet are erased

and whn i come back to vba editor another message appears
"sort method of class failed"

how can i solve that problem?
attachement a picture of my sheet

[L. Howard Kittle]

I am not able to access the picture of the worksheet for some reason. Can't
say why it works on a new worksheet and not on your original.

Regards,
Howard

"amrezzat" <[email protected]> wrote in
message news:[email protected]...
>
> rearrange cells based on its numerical values without repeating
>
> --------------------------------------------------------------------------------
>
> with this sub
>
> Sub doit()
> Range("M1").CurrentRegion.ClearContents
> Range("A1").Resize(10, 2).Copy Destination:=Range("M1")
> Range("M1").CurrentRegion.Sort Key1:=Range("M1"), Order1:=xlAscending
> End Sub
>
>
> it works
> but i dont want to repeat the numbers
> i mean
> if i input
> 1 microsoft
> 2 intel
> 1 microsoft
> the results will be
> 1
> 1
> 2
>
>
> i dont want that
> i want only
> 1
> 2
> no repeating
> how can i do that?
> noting that the input my be so long may be 1000 rows
> but they are only 50 companies (from 1 to 50)
> so the results will be in a max 50 rows
>
> and i want that code to activate(rerun) each time i enter the sheet2
>
> how can i do that ??
>
>
> --
> amrezzat
> ------------------------------------------------------------------------
> amrezzat's Profile:
> http://www.excelforum.com/member.php...o&userid=28766
> View this thread: http://www.excelforum.com/showthread...hreadid=487039
>