Row deletion based on matches
 

Hi,
I got this program from David McRitchie's website and I tweaked it a little
bit.
It works great with alphabets matching in columns A and B. It deletes the
whole row when col A and B contain the same alphabet.
I was wondering how I would change it in order to work with numbers instead
of alphabets. And how can I have it delete the rows that DO NOT match and
keep the ones that match? Any help would be appreciated. Thanks

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = LCase(rng(i).Offset(0, 1).Value) Then
rng(i).EntireRow.Delete

Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Row deletion based on matches
 

Hi Sean

Without code you can do this (see the basic examples first on Debra's site)
http://www.contextures.com/xladvfilter02.html#Match

Select the rows and delete them

If you want a macro post back


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message ...
Hi,
I got this program from David McRitchie's website and I tweaked it a little
bit.
It works great with alphabets matching in columns A and B. It deletes the
whole row when col A and B contain the same alphabet.
I was wondering how I would change it in order to work with numbers instead
of alphabets. And how can I have it delete the rows that DO NOT match and
keep the ones that match? Any help would be appreciated. Thanks

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = LCase(rng(i).Offset(0, 1).Value) Then
rng(i).EntireRow.Delete

Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Row deletion based on matches
 

Hi Ron,
Thanks for the reply. I have about 20 files with atleast 6000 rows of data
in each so I was thinking that a macro would be best. What would you suggest?
Please let me know.
Thanks
Sean

"Ron de Bruin" wrote:

Hi Sean

Without code you can do this (see the basic examples first on Debra's site)
http://www.contextures.com/xladvfilter02.html#Match

Select the rows and delete them

If you want a macro post back


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message ...
Hi,
I got this program from David McRitchie's website and I tweaked it a little
bit.
It works great with alphabets matching in columns A and B. It deletes the
whole row when col A and B contain the same alphabet.
I was wondering how I would change it in order to work with numbers instead
of alphabets. And how can I have it delete the rows that DO NOT match and
keep the ones that match? Any help would be appreciated. Thanks

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = LCase(rng(i).Offset(0, 1).Value) Then
rng(i).EntireRow.Delete

Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Row deletion based on matches
 

Hi Sean

Advanced filter is a bit faster and you can code that also if you want but try this first.

This is a example for numbers

Sub Delete_rows_based_on_ColA_ColB2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlNumbers)
For i = rng.Count To 1 Step -1
If rng(i).Value < rng(i).Offset(0, 1).Value Then
rng(i).EntireRow.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message ...
Hi Ron,
Thanks for the reply. I have about 20 files with atleast 6000 rows of data
in each so I was thinking that a macro would be best. What would you suggest?
Please let me know.
Thanks
Sean

"Ron de Bruin" wrote:

Hi Sean

Without code you can do this (see the basic examples first on Debra's site)
http://www.contextures.com/xladvfilter02.html#Match

Select the rows and delete them

If you want a macro post back


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message ...
Hi,
I got this program from David McRitchie's website and I tweaked it a little
bit.
It works great with alphabets matching in columns A and B. It deletes the
whole row when col A and B contain the same alphabet.
I was wondering how I would change it in order to work with numbers instead
of alphabets. And how can I have it delete the rows that DO NOT match and
keep the ones that match? Any help would be appreciated. Thanks

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = LCase(rng(i).Offset(0, 1).Value) Then
rng(i).EntireRow.Delete

Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Row deletion based on matches
 

Hi Ron,
It works perfectly for me! Thanks so much.
Sean

"Ron de Bruin" wrote:

Hi Sean

Advanced filter is a bit faster and you can code that also if you want but try this first.

This is a example for numbers

Sub Delete_rows_based_on_ColA_ColB2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlNumbers)
For i = rng.Count To 1 Step -1
If rng(i).Value < rng(i).Offset(0, 1).Value Then
rng(i).EntireRow.Delete
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message ...
Hi Ron,
Thanks for the reply. I have about 20 files with atleast 6000 rows of data
in each so I was thinking that a macro would be best. What would you suggest?
Please let me know.
Thanks
Sean

"Ron de Bruin" wrote:

Hi Sean

Without code you can do this (see the basic examples first on Debra's site)
http://www.contextures.com/xladvfilter02.html#Match

Select the rows and delete them

If you want a macro post back


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message ...
Hi,
I got this program from David McRitchie's website and I tweaked it a little
bit.
It works great with alphabets matching in columns A and B. It deletes the
whole row when col A and B contain the same alphabet.
I was wondering how I would change it in order to work with numbers instead
of alphabets. And how can I have it delete the rows that DO NOT match and
keep the ones that match? Any help would be appreciated. Thanks

Sub Delete_rows_based_on_ColA_ColB()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, rng As Range, i As Long
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = LCase(rng(i).Offset(0, 1).Value) Then
rng(i).EntireRow.Delete

Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub