Scan and delete

[Guest]

I am wanting to scan the rows of a spreadsheet and delete the rows where the
values in columns A, B, and C in each row are equal.

Can anyone help me?

 

[Edwin Tam]

Excel VBA for this task could be over complicated and inefficient for your
task. It can be achieved using manual steps.

First of all, I assume all columns have column headings.

1) Insert a column to your data (e.g. at the front). Give it any heading,
and label your rows 1,2,3,4,5,6,7.... (This will be useful for sorting.)
2) Sort your table by columns B, C, then D.
3) In the column next to the last column, in the second cell (in row 2),
enter the formula:
=IF(AND(B2=B1,C2=C1,D2=D1),TRUE,FALSE)
4) Give the new column in (3) a column heading
5) Select all the cells in the new column, COPY, and PASTE SPECIAL, PASTE
VALUES.
5) Sort the table base on the new column, in Descending order
(Now, you got all the TRUE's on top.)
6) Delete all the TRUE rows.
7) Delete the TRUE/FALSE column
8) Sort the data by column A, in Ascending order
9) Delete column A.

Of course, the above steps can be automated. But it could be too time
consuming. It's always better to do it manually.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Edwin Tam]

Or, if you do this regularly and need to scan different number of columns
under different situations, you can have a look at "Excel Power Expander".
It has an automation function called "Duplicated Rows Hunter". It can scan
up to 256 columns, which means it can compare ALL columns in your
spreadsheet for duplicated items. It also allows you to either DELETE or
MOVE or HIGHTLIGHT the duplicated rows. And you can even compare two lists
in different locations.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Guest]

Steps 4, 5, 5 and 7 can be eliminated by using filtering and the select
visible cells function.

 

[Don Lloyd]

Hi,
If you would prefer it to be done with VBA, try the following.

Sub ScanAndDelete()
Dim Base, X, Rw, BtmRw
Base = Cells.Rows.Count
'find last row to check
For X = 1 To 3
Rw = Cells(Base, X).End(xlUp).Row
If Rw > BtmRw Then BtmRw = Rw
Next
'scan and delete
For X = BtmRw To 1 Step -1
If Cells(X, 1) = Cells(X, 2) And Cells(X, 2) = Cells(X, 3) Then
Rows(X).EntireRow.Delete
End If
Next
End Sub

This will delete all rows where A,B & C are equal right up to row 1.
If your start row is not Row 1, then in the line
For X = BtmRw To 1 Step -1
change the first 1 to your start row. e.g. If your data starts at row 5
then
For X = BtmRw To 5 Step -1

Note that if rows A,B and C are all blank will also be deleted.

regards,
Don

 

[Myrna Larson]

it can compare ALL columns in your
spreadsheet for duplicated items.

Just as you can do with Advanced Filter/Unique Records. How does your solution
differ?