Deleting entire rows when several cells match

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

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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>
>
>