Scan and remove column data query
 

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.

Scan and remove column data query
 

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

On Mar 21, 11:35 am, "IE" wrote:
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.

Scan and remove column data query
 

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" wrote:

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.

Scan and remove column data query
 

"IE" wrote in message
...
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.

Scan and remove column data query
 

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

On Mar 22, 10:55 am, "IE" wrote:
"IE" wrote in message

...



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 tohttp://iedmont.blogspot.com/I have posted what the result should look like.
I think your solutions are deleting entire rows?

Thanks.

IE.

Scan and remove column data query
 

Brilliant! Thanks very much for your help!

IE.

"vezerid" wrote in message
oups.com...
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

On Mar 22, 10:55 am, "IE" wrote:
"IE" wrote in message

...



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
tohttp://iedmont.blogspot.com/I have posted what the result should look
like.
I think your solutions are deleting entire rows?

Thanks.

IE.