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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

maytas

New Member
Joined
Sep 5, 2006
Messages
18
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
 

comtrex

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

New Member
Joined
Sep 5, 2006
Messages
18
I do not understend your 2. question exactly.
Can you show example for your question in Excel Worksheet?
 

comtrex

New Member
Joined
Sep 12, 2006
Messages
15

ADVERTISEMENT

Example

11111

2222

3333

finish
1 save 1111 delete

2 save 222 delete

3 save 333 delete

Regards

Comtrex
 

maytas

New Member
Joined
Sep 5, 2006
Messages
18
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
 

comtrex

New Member
Joined
Sep 12, 2006
Messages
15

ADVERTISEMENT

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

New Member
Joined
Sep 5, 2006
Messages
18
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

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
FYI - you don't need a macro. You can just use advanced filter's unique records only tool.
 

Forum statistics

Threads
1,137,202
Messages
5,680,145
Members
419,886
Latest member
Pulpboi

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
Top