Scan and remove column data query

[IE]

Hi,

I have a spreadsheet (http://iedmont.blogspot.com/) and am trying to find a
solution to scan column A for duplicates and if found, remove the cell
contents of the active cell, column B, column, C and column D.

So as an example in the example sheet, it would scan column A for data1,
leave cell contents of A2, B2, C2, D2, E2 and F2 in place and remove cell
contents A3 to D5. Then keep all cell contents in row 6 (only one entry
found for data2). Then keep all row 7 cell contents and remove A8 to D13,
leave row 14 and finally leave row 15 and remove A16 to D16. All data in
columns E and F to remain.

Can anyone help please?

Many thanks.

IE.

 

[vezerid]

Sub DeleteDupRows()
i = 3
While Cells(i, 1) <> 0
If Cells(i, 1) = Cells(i - 1, 1) Then
Cells(i, 1).EntireRow.Delete
Else
i = i + 1
End If
Wend
End Sub

HTH
Kostis Vezerides

 

[Billy Liddel]

Or

Sub DeleteDupes()
col = ActiveCell.Column
startR = ActiveCell.Row: lastR = Selection.Rows.Count
On Error Resume Next
For i = lastR To startR Step -1
c = Cells(i, col)
Set rng = Range(Cells(1, col), Cells(lastR, col))
x = WorksheetFunction.CountIf(rng, c)
If x > 1 Then
Rows(i).EntireRow.Delete
lastR = lastR - 1
End If
Next
End Sub

But vezerid's seem briefer -

Peter

 

[IE]

Hi,

I have a spreadsheet (http://iedmont.blogspot.com/) and am trying to find
a solution to scan column A for duplicates and if found, remove the cell
contents of the active cell, column B, column, C and column D.

So as an example in the example sheet, it would scan column A for data1,
leave cell contents of A2, B2, C2, D2, E2 and F2 in place and remove cell
contents A3 to D5. Then keep all cell contents in row 6 (only one entry
found for data2). Then keep all row 7 cell contents and remove A8 to D13,
leave row 14 and finally leave row 15 and remove A16 to D16. All data in
columns E and F to remain.

Can anyone help please?

Many thanks.

IE.

Sorry guys. May be I din't explain very well but if you go to
http://iedmont.blogspot.com/ I have posted what the result should look like.
I think your solutions are deleting entire rows?

Thanks.

IE.

 

[vezerid]

Apologies... I don;t know how we both reached the same conclusion...

Sub ClearDupRows()
lastVal = Cells(2, 1)
i = 3
While Cells(i, 1) <> ""
If Cells(i, 1) = lastVal Then
Range("A" & i & "" & i).ClearContents
Else
lastVal = Cells(i, 1)
End If
i = i + 1
Wend
End Sub

HTH
Kostis Vezerides

 

[IE]

Brilliant! Thanks very much for your help!

IE.

Apologies... I don;t know how we both reached the same conclusion...

Sub ClearDupRows()
lastVal = Cells(2, 1)
i = 3
While Cells(i, 1) <> ""
If Cells(i, 1) = lastVal Then
Range("A" & i & "" & i).ClearContents
Else
lastVal = Cells(i, 1)
End If
i = i + 1
Wend
End Sub

HTH
Kostis Vezerides