Different no headers and this vba

[fredhedge]

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?

[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]

 

[JLGWhiz]

This is the only line that I saw that would make any difference about the headers.

x = Application.Match(ws2.Cells(i, sh2Col), ws1.Range(sh1Col & "[COLOR=#ff0000]3[/COLOR]:" & sh1Col & r1), 0)

Just change the row as shown in red above. Just remember that the Match function is returning the relative position of the value being seached for. Not the actual row number. So in this case the number returned for value of 'x' will be two more than the actual row number.