Previous Topic Next Topic

Compare two columns text

Closed
Zak - Jun 28, 2012 at 03:13 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 - Jun 28, 2012 at 06:55 AM
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
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 808
Jun 28, 2012 at 06:55 AM 
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
0