to eliminate double

comtrex

New Member
Joined
Sep 12, 2006
Messages
15
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi.
Is This?

Code:
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
 
Upvote 0
The macro and good for my purpose thanks, I would have a variation question can maintain a value of the double ones

Regards

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

11111

2222

3333

finish
1 save 1111 delete

2 save 222 delete

3 save 333 delete

Regards

Comtrex
 
Upvote 0
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>

Code:
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top