Easy Macro question!!

ericam

New Member
Joined
Jul 24, 2005
Messages
20
Would anyone know the code for this:
I have many rows and columns of data in a spreadsheet. Columns A and B are fairly similar but not exactly. If column B’s data is not the same as the corresponding data in column A then the whole row from column B onwards should be deleted but not column A. Basically, column A is the determining factor if column B and onwards should remain in spreadsheet. But nothing in column A should ever be deleted.
For instance if these are the columns and rows:
A B C D
1 1 4 9
2 1 5 7
4 2 8 6
…4 5 0

After i have sorted it, it should look like this

A B C D
1 1 4 9
2 2 8 6
4 4 5 0

Column B has more rows than Column A but it should equalize after the filtering is done. I have tried using the filter, but it does not do that type of filtering.
Thank you very very much in advance!!!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
If your data in column A is contiguous (no empty cells) this might do what you want:


Sub Test1()
Application.ScreenUpdating = 0
Dim cell As Range
For Each cell In Columns(1).SpecialCells(2)
With cell
If .Value <> .Offset(0, 1).Value Then _
Range(Cells(.Row, 2), Cells(.Row, Cells.Columns.Count)).Delete Shift:=xlUp
End With
Next cell
Application.ScreenUpdating = 1
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,129
Messages
5,576,267
Members
412,710
Latest member
Maged elmasry
Top