Deleting entire rows when several cells match

S

Steve

Hey Folks,
I have a program that generates 10,000 rows of information.
After sorting them by a few columns, I end up with around 6,000 more rows
than I need since there are many near duplicates.
What I would like to be able to do is run a macro that essentially would
read something like this :
if A5 = A4 and B5 = B4 and C5 = C4 then delete row 5
Any help would be appreciated.
Each row has about 30 columns in it and no entire row ever matches any other
row, so just matching a few cells of the row above it is all I need to
determine if the next row needs to be deleted.
Thank you for any help.
Steve
 
R

Richard.Toren

Subject: Re: Deleting entire rows when several cells match

Create a column so that it is either 1 or 0 depending on if the row above it "match'es"

Put a 0 in the first row (doesn't match) and in all subsequent rows use an equation like:
=if( a2=a1 and b2=b1 and c2=c1, 1, 0)

Now you can use the autofilter to show only the rows where the column is 1
select them and delete them.

The macro looks something like this

Range("B1").Select
ActiveCell.FormulaR1C1 = "0"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B35")
Columns("A:B").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("1:33").Select
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft

But you'd like to have the range not be fixed, so it is a bit more work


-----Original Message-----
 
S

Steve

I must have been having a brain freeze, normally I could think of this one -
simple and does exactly what I need.
Thank you so much.

Richard.Toren said:
Subject: Re: Deleting entire rows when several cells match

Create a column so that it is either 1 or 0 depending on if the row above
it "match'es"

Put a 0 in the first row (doesn't match) and in all subsequent rows use an
equation like:
=if( a2=a1 and b2=b1 and c2=c1, 1, 0)

Now you can use the autofilter to show only the rows where the column is 1
select them and delete them.

The macro looks something like this

Range("B1").Select
ActiveCell.FormulaR1C1 = "0"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],1,0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B35")
Columns("A:B").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
Rows("1:33").Select
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft

But you'd like to have the range not be fixed, so it is a bit more work


-----Original Message-----
Hey Folks,
I have a program that generates 10,000 rows of information.
After sorting them by a few columns, I end up with around 6,000 more rows
than I need since there are many near duplicates.
What I would like to be able to do is run a macro that essentially would
read something like this :
if A5 = A4 and B5 = B4 and C5 = C4 then delete row 5
Any help would be appreciated.
Each row has about 30 columns in it and no entire row ever matches any
other
row, so just matching a few cells of the row above it is all I need to
determine if the next row needs to be deleted.
Thank you for any help.
Steve
Click to expand...
Click to expand...
 
Top