Scan and delete
 

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?

Scan and delete
 

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

http://www.vonixx.com



On 1/12/05 10:53 AM, in article
, "Hutch"
wrote:

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?

Scan and delete
 

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

http://www.vonixx.com



On 1/12/05 10:53 AM, in article
, "Hutch"
wrote:

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?

Scan and delete
 

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

"Edwin Tam" wrote:

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

http://www.vonixx.com



On 1/12/05 10:53 AM, in article
, "Hutch"
wrote:

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?

Scan and delete
 

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


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

Scan and delete
 

On Wed, 12 Jan 2005 11:18:22 +0800, Edwin Tam wrote:

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?