Scan and remove column data query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

IE - 21 Mar 2007 09:35 GMT

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.

Reply to this Message

vezerid - 21 Mar 2007 13:00 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
>
> IE.

Reply to this Message

Billy Liddel - 21 Mar 2007 13:45 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
>
> IE.

Reply to this Message

IE - 22 Mar 2007 09:55 GMT

> Hi,
>
[quoted text clipped - 14 lines]
>
> 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.

Reply to this Message

vezerid - 22 Mar 2007 13:27 GMT

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 & ":D" & i).ClearContents
Else
lastVal = Cells(i, 1)
End If
i = i + 1
Wend
End Sub

HTH
Kostis Vezerides

> > Hi,
>
[quoted text clipped - 21 lines]
>
> IE.

Reply to this Message

IE - 23 Mar 2007 10:50 GMT

Brilliant! Thanks very much for your help!

IE.

> Apologies... I don;t know how we both reached the same conclusion...
>
[quoted text clipped - 47 lines]
>>
>> IE.

Reply to this Message