I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

Try this:

Sub RemoveDupes()
Range("A1").Select
Columns("A:A").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Columns( _
"B:B"), Unique:=True
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
End Sub

Regards,
Stefi

€žFelix€� ezt Ã*rta:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

I tried to write this macro:

It would not accept the following line(s)

Columns("A:A").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Columns( _
"B:B"), Unique:=True


--
Felix


"Stefi" wrote:

Try this:

Sub RemoveDupes()
Range("A1").Select
Columns("A:A").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Columns( _
"B:B"), Unique:=True
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
End Sub

Regards,
Stefi

€žFelix€� ezt Ã*rta:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

Sub RemoveDupes1()

With Cells

Set rng = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
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).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
--
Best wishes,

Jim


"Felix" wrote:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

For Column "A"

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))

Forgot to make the change.
--
Best wishes,

Jim


"Jim Jackson" wrote:

Sub RemoveDupes1()

With Cells

Set rng = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
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).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
--
Best wishes,

Jim


"Felix" wrote:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

Hello Jim,

I tried your macro as follows:

Col. A with data (numbers) (sorted)

Then I selected the cells ( A1:A313) and ran the macro with corrected line:

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rng.Select

I get Error message with "400" before the End If


--
Felix


"Jim Jackson" wrote:

For Column "A"

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))

Forgot to make the change.
--
Best wishes,

Jim


"Jim Jackson" wrote:

Sub RemoveDupes1()

With Cells

Set rng = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
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).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
--
Best wishes,

Jim


"Felix" wrote:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

I ran it again to make sure I didn't screw anything up and it worked just
fine for me. What message did the error message have?
--
Best wishes,

Jim


"Felix" wrote:

Hello Jim,

I tried your macro as follows:

Col. A with data (numbers) (sorted)

Then I selected the cells ( A1:A313) and ran the macro with corrected line:

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rng.Select

I get Error message with "400" before the End If


--
Felix


"Jim Jackson" wrote:

For Column "A"

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))

Forgot to make the change.
--
Best wishes,

Jim


"Jim Jackson" wrote:

Sub RemoveDupes1()

With Cells

Set rng = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
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).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
--
Best wishes,

Jim


"Felix" wrote:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix

Sorry Jim,

now it works...

I must have screwed up something running it.

I had to sort first so that all duplicates are together. This is necessary,
I guess. OK?
--
Felix


"Jim Jackson" wrote:

I ran it again to make sure I didn't screw anything up and it worked just
fine for me. What message did the error message have?
--
Best wishes,

Jim


"Felix" wrote:

Hello Jim,

I tried your macro as follows:

Col. A with data (numbers) (sorted)

Then I selected the cells ( A1:A313) and ran the macro with corrected line:

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rng.Select

I get Error message with "400" before the End If


--
Felix


"Jim Jackson" wrote:

For Column "A"

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))

Forgot to make the change.
--
Best wishes,

Jim


"Jim Jackson" wrote:

Sub RemoveDupes1()

With Cells

Set rng = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
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).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
--
Best wishes,

Jim


"Felix" wrote:

I have a single column with numbers from which I want to eliminate duplicates.
The colums can be sorted, but one number for which duplicate(s) exist must
remain.

There are, however, no fixed amount of entries in the column.

I tried

Sub RemoveDupes()

'Add extra Column, "A" becomes "B"
Columns(1).EntireColumn.Insert

'Filter out duplicates and copy unique list to "A"
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete


but nothing happens...


--
Felix