Compare two columns text [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

If the text of any cell in column-A matches with the text of any cell in column-B, copy entire row to next sheet.

Like

Column-A Column-B
Private Car
Private Motorcycle
Private Car
Private Motorcycle
Private Car
Private Motorcycle
Private Bus
Private Bus

copy entire rows of Private Cars to next sheet

Private Car
Private Car
Private Car

kindly provide me the Macro coding solution for above

Regards

Zak


1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
Sub test()
Dim r As Range, filt As Range
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Set r = .Range("a1").CurrentRegion
r.AutoFilter field:=1, Criteria1:="private"
r.AutoFilter field:=2, Criteria1:="car"
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(12)
filt.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
.AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!