I have a spreadsheet with lots of duplicate rows of data.
I have run =IF(COUNTIF($A$2:A2,A2)=1,"-","Delete") to see
where duplicates appear and this is fine. I have 1,576
records of which I only need to keep 645.

What I would like to know is, how would I run a macro to
delete any rows automatically and move the the deleted
lines up so there are no blank lines between the remaining
information?

Any help is greatly appreciated.

Many thanks

Malcolm

Hi Malcolm

this macro deletes rows, which have the same content in
column A. It is sorting the rows and then deleting the
doubles.
You may have to modify it for your needs.

Sub Sort_data_delete_double()

Worksheets("Sheet1").Range("A1").Sort _
key1:=Worksheets("Sheet1").Range("A1")
Set currentCell = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
End Sub

Wolf


-----Original Message-----
I have a spreadsheet with lots of duplicate rows of
data.
I have run =IF(COUNTIF($A$2:A2,A2)=1,"-","Delete") to see
where duplicates appear and this is fine. I have 1,576
records of which I only need to keep 645.

What I would like to know is, how would I run a macro to
delete any rows automatically and move the the deleted
lines up so there are no blank lines between the
remaining
information?

Any help is greatly appreciated.

Many thanks

Malcolm
.

One more (non programic) help

If you would like to delete duplicated rows you can do following.
1)Select all data in your sheet where you want to delete duplicate rows
2) Data - Filter - Extended filter and check the Checkbox Without duplicite
records (maybe its called little bit differently, I have Czech version of
Excel, so I'm just translating it to EN ;-))

Then Copy and Paste the data to different Sheet

Good luck

:-))

Filip






"wolf" píse v diskusním príspevku
...
Hi Malcolm

this macro deletes rows, which have the same content in
column A. It is sorting the rows and then deleting the
doubles.
You may have to modify it for your needs.

Sub Sort_data_delete_double()

Worksheets("Sheet1").Range("A1").Sort _
key1:=Worksheets("Sheet1").Range("A1")
Set currentCell = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
End Sub

Wolf


-----Original Message-----
I have a spreadsheet with lots of duplicate rows of
data.
I have run =IF(COUNTIF($A$2:A2,A2)=1,"-","Delete") to see
where duplicates appear and this is fine. I have 1,576
records of which I only need to keep 645.

What I would like to know is, how would I run a macro to
delete any rows automatically and move the the deleted
lines up so there are no blank lines between the
remaining
information?

Any help is greatly appreciated.

Many thanks

Malcolm
.