Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#Ref! after row deletion | Excel Discussion (Misc queries) | |||
Row Deletion based on condition. | Excel Discussion (Misc queries) | |||
name deletion | Excel Discussion (Misc queries) | |||
simple row deletion | Excel Discussion (Misc queries) | |||
Row Deletion | Excel Programming |