to eliminate double

[comtrex]

You know how to point out me a macro to remove the double ones in a whole cells A1:A10.

Regards and graces

Comtrex


Sabéis indicarme un macro para sacar los dobles en un conjunto de celdas A1:A10.

Saludos y gracias

Comtrex

 

[maytas]

Hi.
Is This?

Sub Listing()
    List_MyRange Range("A1:A10")
End Sub

Sub List_MyRange(MyRange As Range)
    Dim MyCell As Range, MyList As New Collection, Counter As Integer
    Application.Volatile
    On Error Resume Next
    For Each MyCell In MyRange
        MyList.Add MyCell.Value, CStr(MyCell.Value)
    Next MyCell
    On Error GoTo 0
    Range("A1:A10").ClearContents
    For Counter = 1 To MyList.Count
        Range("A" & Counter) = MyList.Item(Counter)
    Next Counter
End Sub

 

[comtrex]

The macro and good for my purpose thanks, I would have a variation question can maintain a value of the double ones

Regards

Comtrex

 

[maytas]

I do not understend your 2. question exactly.
Can you show example for your question in Excel Worksheet?

 

[comtrex]

Example

11111

2222

3333

finish
1 save 1111 delete

2 save 222 delete

3 save 333 delete

Regards

Comtrex

 

[maytas]

Hi, is this?

Before:
<table border=0 bordercolor=gray bgcolor=gray Valign=CENTER><tr><td align=center bgcolor=gray><font color=white size=2></font></td><td align=center bgcolor=gray><font color=white size=2>A</font></td></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>1</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>1111</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>2</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>1234</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>3</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>2222</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>4</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>2345</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>5</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>3333</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>6</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>3456</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>7</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>4444</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>8</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>4567</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>9</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>5555</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>10</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>5678</font></td></tr></table>

After:
<table border=0 bordercolor=gray bgcolor=gray Valign=CENTER><tr><td align=center bgcolor=gray><font color=white size=2></font></td><td align=center bgcolor=gray><font color=white size=2>A</font></td></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>1</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>1234</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>2</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>2345</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>3</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>3456</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>4</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>4567</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>5</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>5678</font></td></tr></table>

Sub Test()
    Dim MyCell As Range
    For Each MyCell In Range("A1:A10")
        If StrComp(WorksheetFunction.Rept(Mid(MyCell.Value, 1, 1), Len(MyCell.Value)), MyCell.Value, vbTextCompare) = 0 Then MyCell.Delete Shift:=xlUp
    Next
End Sub

 

[comtrex]

I thank for the help

a small error

what it doesn't cancel as example

11111


111 delete good

1 finish good good

1
1
1
12
12
12
3
13
3
13
2
2
2

finish actual

1
12
12
12
13
13
2
2
2

Finish good perfect


1
12
13
3

and possible or no

I thank for the help

Regards
Comtrex

 

[maytas]

Hi,
Sorry, but I am not sure that i understand you correctly.
For your last example,If you run my first makro, than you will be following resultate:

<table border=0 bordercolor=gray bgcolor=gray Valign=CENTER><tr><td align=center bgcolor=gray><font color=white size=2></font></td><td align=center bgcolor=gray><font color=white size=2>A</font></td></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>1</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>1</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>2</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>12</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>3</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>3</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>4</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>13</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>5</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>2</font></td></tr></table>

Is this?

 

[comtrex]

Done example and good perfect, so it has to result.


<table border=0 bordercolor=gray bgcolor=gray Valign=CENTER><tr><td align=center bgcolor=gray><font color=white size=2></font></td><td align=center bgcolor=gray><font color=white size=2>A</font></td></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>1</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>1</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>2</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>12</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>3</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>3</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>4</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>13</font></td></tr></tr><tr height=8><td align=center bgcolor=gray width=10><font color=white size=2>5</font></td><td bgcolor=FFFFFF align=right><font size=2 color=black face=Arial>2</font></td></tr></table>




Regards
Comtrex

 

[Greg Truby]

FYI - you don't need a macro. You can just use advanced filter's unique records only tool.