Previous Topic Next Topic

Compare two columns text

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
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

Answer 1 / 1
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 802 
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
Ask a question