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" wrote in
message ...

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