Compare two columns and delete cells

Closed
Mitch - Mar 31, 2010 at 02:29 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 31, 2010 at 09:19 PM
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

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Mar 31, 2010 at 09:19 PM
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
0