Compare two columns and delete cellsClosed

Question

I have 3 columns in Excel 2007.  The following is the sample data.  Col3 is a formula that compares Col1 and Col2.  If the value in the two columns match, then return True, else return False.  I need a macro to remove the cells in Col2 and Col3 if Col3 is False.  Once the cells removed, the cells on the Col2 and Col3 below the removed cells will move up.  The expected result is shown below.  Any help would be great!

Col1          Col2        Col3
AAA           AAA         True
CCC          BBB          False
DDD          CCC         False 
                 DDD         False

The expected result:

Col1          Col2        Col3
AAA           AAA         True
CCC          CCC         True
DDD          DDD         True

venkat1926 · Answer

quote 
Col1 Col2 Col3  
AAA AAA True  
CCC BBB False  
DDD CCC False  
DDD False 
unquote  

I suppose in the last row col.1 is blank col2 is DDD and column 3 is the formula  

keep the formulas in col.. 3in all the rows.  


copy data in sheet 1 in sheet 3 (from  cell A1 also (this is for undo purposes) 

AS YOU ARE MESSING UP WITH THE DATA  SAVE THE DATA BASE SAFELY SOMEWHERE BEFORE RUNNING THE MACRO

try the macro on the sample sheets first and then try in original file later  

the undo macro undoes what the macro has done provided the data is availabel in sheet 3 also as instructed before.  

the macros are  

Sub test() 
Dim r As Range, c As Range 
Set r = Range(Range("C1"), Range("C1").End(xlDown)) 
For Each c In r 
If c = False Then 
c.Offset(1, -1).Copy c.Offset(0, -1) 
If c.Offset(0, -1) = "" Then 
c.EntireRow.Delete 
End If 
End If 
Next c 
End Sub


Sub undo() 
Worksheets("sheet1").Cells.Clear 
Worksheets("sheet3").Cells.Copy 
Worksheets("sheet1").Range("A1").PasteSpecial 
End Sub

Compare two columns and delete cells

1 response

Similar discussions

Subscribe To Our Newsletter!