Delete rows if match between xlsx files

[Crocdundee]

Purpose: delete a row in W2b.xlsx file if a match is not found in utab.slsx file
colums to match is A:A IN W2B.XLSX AND A:A IN UTAB.SLSX

NOTE: In the last column (6) there ia a row missing which is row 7

I have been trying match to no avail, and I am now not so sure this is the way to go.

Help is always appreciated from the experts
Thanks

using office 2007

w2b.xlsx file

40703ROCK0101 09/06/11 ROCKHAMPTON 01 01
40703ROCK0102 09/06/11 ROCKHAMPTON 01 02
40703ROCK0103 09/06/11 ROCKHAMPTON 01 03
40703ROCK0104 09/06/11 ROCKHAMPTON 01 04
40703ROCK0105 09/06/11 ROCKHAMPTON 01 05
40703ROCK0106 09/06/11 ROCKHAMPTON 01 06
40703ROCK0107 09/06/11 ROCKHAMPTON 01 07
40703ROCK0108 09/06/11 ROCKHAMPTON 01 08
40703ROCK0109 09/06/11 ROCKHAMPTON 01 09
40703ROCK0110 09/06/11 ROCKHAMPTON 01 10
40703ROCK0111 09/06/11 ROCKHAMPTON 01 11
40703ROCK0112 09/06/11 ROCKHAMPTON 01 12


utab.slsx

40703Rock0101 09/06/11 Rockhampton 01 01
40703Rock0102 09/06/11 Rockhampton 01 02
40703Rock0103 09/06/11 Rockhampton 01 03
40703Rock0104 09/06/11 Rockhampton 01 04
40703Rock0105 09/06/11 Rockhampton 01 05
40703Rock0106 09/06/11 Rockhampton 01 06
40703Rock0108 09/06/11 Rockhampton 01 08
40703Rock0109 09/06/11 Rockhampton 01 09
40703Rock0110 09/06/11 Rockhampton 01 10
40703Rock0111 09/06/11 Rockhampton 01 11
40703Rock0112 09/06/11 Rockhampton 01 12
------------------------------------------------------
Help here will be appreciated
Graham

 

[kpark91]

Assuming your data starts from Row 2..

I have not tested this code.

Option Explicit
Sub kpark91()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    Dim sCount As Double, dCount As Double, sLR As Double, dLR As Double
    Dim sWS As Worksheet, dWS As Worksheet
    dCount = 2
    sCount = 2
    Set dWS = Workbooks("w2b.xlsx")
    Set sWS = Workbooks("utab.xlsx")
    dLR = dWS.Range("A" & Rows.Count).End(xlUp).Row
    sLR = sWS.Range("A" & Rows.Count).End(xlUp).Row
 
    Do While dCount <= dLR
        sCount = 2
        Do While sCount <= sLR
            If sWS.Range("A" & sCount).Value = dWS.Range("A" & dCount).Value Then
                GoTo Continue
            End If
            sCount = sCount + 1
        Loop
        dWS.Range("A" & dCount).EntireRow.Delete
Continue:
        dCount = dCount + 1
    Loop
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

 

[Crocdundee]

Thanks for the reply.
opened both workbooks
Created a module and pasted the code
and ran the code.
Get this message

Run-Time Error '9':
Subscript out of range

Always stops on
Set dWS = Workbooks("w2b.xlsx")
any Ideas ????
Regards
Graham

 

[kpark91]

Try

Set dWS = Workbooks("w2b")