Hi everybody
I have found this VBA and I want it to remove rows on sheet1 when its not found on sheet2. However, I have 1-row-header in sheet2 and 2-row-header in sheet1. I get the code only to work when both sheets have 1 row header. Can anybody of you please help me adjust the code?
I have found this VBA and I want it to remove rows on sheet1 when its not found on sheet2. However, I have 1-row-header in sheet2 and 2-row-header in sheet1. I get the code only to work when both sheets have 1 row header. Can anybody of you please help me adjust the code?
Code:
[COLOR=#000000][FONT='inherit']Sub DeleteNotMatch22()[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Const sh1Col As String = "A"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Const sh2Col As String = "A"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Dim ws1 As Worksheet, ws2 As Worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Dim r1 As Long, r2 As Long, i As Long, x As Long[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Set ws1 = Sheets("Sheet1") 'This one has 2 row header.[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Set ws2 = Sheets("Sheet2") ' This one has 1 row header. [/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']r1 = ws1.Cells(Rows.Count, sh1Col).End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']r2 = ws2.Cells(Rows.Count, sh2Col).End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']On Error Resume Next[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']For i = 2 To r2[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']x = Application.Match(ws2.Cells(i, sh2Col), ws1.Range(sh1Col & "1:" & sh1Col & r1), 0)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']ws1.Cells(x, 255) = "xx"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Next i[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']ws1.Cells(1, 255) = "xx"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Intersect(ws1.UsedRange, ws1.Columns(255)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']ws1.Columns(255).ClearContents[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]