VBA runtime error

[strongman86]

Lads,

I have issue with my manually entered data on excel spreadsheet misaligning when data is refreshed from MS query e.g row is added or removed by query. I have came across this:
https://www.techsupportforum.com/forums/f57/mis-aligned-data-on-data-refresh-565373.html
I have tried to follow instructions, but when I run VBA I get run-time error '9': Subscript out of range.

In my case:
1) Unique Identifier is in column B (alphanumeric)
2) Yes I have headers
3) Extracted data worksheet name: MdProductDataList
4) Updated data worksheet name: Data
5) Excel is version 2013

Code I'm using:

Sub test()
Dim cnt, rw As Integer
Dim val As Variant


cnt = Worksheets("Data").Range("B20000").End(xlUp).Row
On Error Resume Next


For rw = cnt To 2 Step -1
val = Empty
val = Application.WorksheetFunction.VLookup(Worksheets("Data").Range("B" & rw).Value, Worksheets("MdProductDataList").Range("B:B"), 1, False)
If val = Empty Then
Worksheets("Data").Rows(rw).Delete
End If
nxt:
Next rw
End Sub

Debug points out this:

cnt = Worksheets("Data").Range("B20000").End(xlUp).Row

How can I fix this? Thanks.

 

[strongman86]

Guys to clear things:
when I run Marco

 

[strongman86]

Guys to clear things:

When row of data is deleted by refreshing data from query and when I run Marco it deletes row and manually entered data aligns with query table (which is what I want).

When one row of data is added by refreshing data from query and when I run Marco no row is added and manually entered data shifts by one row and all my manually added data below this row is misaligned.

I'm after code that not only deletes rows, but does adding as well.

Thanks in advance.

 

[strongman86]

Hope this can be achieved.

 

[strongman86]

Hope this can be achieved.

Anybody?