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!!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,632
Messages
5,832,774
Members
430,167
Latest member
Gogogoben

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top