Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hi,
Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hi,
This compares the column you are in to the column that is just to the of that column. Hope it helps. Sub TwoColumns() Do Until ActiveCell.Value = "" Column1 = ActiveCell.Value Column2 = ActiveCell.Offset(0, 1).Value If Column1 = Column2 Then Stop Else ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Range("A1").Select End If ActiveCell.Offset(1, 0).Range("A1").Select Loop End Sub Thanks, "Robert" wrote: Hi, Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hello David,
Thanks for the reply. I tried the macro but it has some problems. Everytime theres a match or not, it gives a Run-time error '1004': Application-defined or object-defined error. When theres a match, it gives an error at the "STOP" command. And when theres no match it points at ActiveCell.Offset(-1, 0).Range("A1").Select. Any other pointers would be really helpful. Thanks for your help. "David" wrote: Hi, This compares the column you are in to the column that is just to the of that column. Hope it helps. Sub TwoColumns() Do Until ActiveCell.Value = "" Column1 = ActiveCell.Value Column2 = ActiveCell.Offset(0, 1).Value If Column1 = Column2 Then Stop Else ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Range("A1").Select End If ActiveCell.Offset(1, 0).Range("A1").Select Loop End Sub Thanks, "Robert" wrote: Hi, Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hi,
The stop can be removed sorry about that. Can you tell me how your data is set up, which columns you are trying to compare? ie ColA ColB ColC ColD ... etc 5 9 a z .....Etc Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set up to start on the first value to compare and then compare it to the nest column to the right, ie ColA and ColB or ColD and ColE. Thanks, "Robert" wrote: Hi, Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hello,
Do I need to replace the STOP with anything else? My data is set this way. ColA ColB ColC ColD ColE ColF 5/1/05 1 8 25 0.552 3 5/2/05 2 3 47 0.67 3 5/3/05 3 7 8 0.22 3 So for example, I need to compare column F with Column B and everytime there is a common number in both columns I need to keep the whole row of data. Otherwise I have to delete the row if Columns B and F dont match. Hope it is more clearer now. Thanks for the help. Rob "David" wrote: Hi, The stop can be removed sorry about that. Can you tell me how your data is set up, which columns you are trying to compare? ie ColA ColB ColC ColD ... etc 5 9 a z .....Etc Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set up to start on the first value to compare and then compare it to the nest column to the right, ie ColA and ColB or ColD and ColE. Thanks, "Robert" wrote: Hi, Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hi,
This assumes the data you will compare begins in B1, which means there are no Headers, if there are headers change the second line of the code from "Range("B1").Select" to "Range("B2").Select. Sub TwoColumns() Range("B1").Select Do Until ActiveCell.Value = "" Column1 = ActiveCell.Value Column2 = ActiveCell.Offset(0, 4).Value If Column1 = Column2 Then Else ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Range("A1").Select End If ActiveCell.Offset(1, 0).Range("A1").Select Loop End Sub Thanks, "Robert" wrote: Hello, Do I need to replace the STOP with anything else? My data is set this way. ColA ColB ColC ColD ColE ColF 5/1/05 1 8 25 0.552 3 5/2/05 2 3 47 0.67 3 5/3/05 3 7 8 0.22 3 So for example, I need to compare column F with Column B and everytime there is a common number in both columns I need to keep the whole row of data. Otherwise I have to delete the row if Columns B and F dont match. Hope it is more clearer now. Thanks for the help. Rob "David" wrote: Hi, The stop can be removed sorry about that. Can you tell me how your data is set up, which columns you are trying to compare? ie ColA ColB ColC ColD ... etc 5 9 a z .....Etc Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set up to start on the first value to compare and then compare it to the nest column to the right, ie ColA and ColB or ColD and ColE. Thanks, "Robert" wrote: Hi, Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data in columns and delete if not equal
Hi Rob,
This code would work for you, try it out. Thanks to Ron. Just change the Column from A to whatever you want and if you need to compare it to a column other than the next one, change the OFFSET value. Hope it helps. 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 _______ Best regards, Sean "Robert" wrote: Hello, Do I need to replace the STOP with anything else? My data is set this way. ColA ColB ColC ColD ColE ColF 5/1/05 1 8 25 0.552 3 5/2/05 2 3 47 0.67 3 5/3/05 3 7 8 0.22 3 So for example, I need to compare column F with Column B and everytime there is a common number in both columns I need to keep the whole row of data. Otherwise I have to delete the row if Columns B and F dont match. Hope it is more clearer now. Thanks for the help. Rob "David" wrote: Hi, The stop can be removed sorry about that. Can you tell me how your data is set up, which columns you are trying to compare? ie ColA ColB ColC ColD ... etc 5 9 a z .....Etc Then tell what you are comparing ColA and ColB or ColD and ColZ. It is set up to start on the first value to compare and then compare it to the nest column to the right, ie ColA and ColB or ColD and ColE. Thanks, "Robert" wrote: Hi, Im new to Macros and I was wondering if there is a simple macro to compare two columns and see if they have the same value. If the columns dont match, I need to delete the whole row. Any guidance would be really appreciated. Thanks Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing data between two columns | Excel Discussion (Misc queries) | |||
Comparing data in two columns | Excel Worksheet Functions | |||
Comparing columns of data | Excel Discussion (Misc queries) | |||
Comparing Data in 2 columns | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |