Macro keep making excel go to non responding.

Originally Posted by gareth7412

if there is no data in coulmns A-D on a particular row in sheet called OLD then I need that row completely deleting from the new sheet. The data in columns J & K in both sheets will be exactly the same as they are unique

According to your attachment a VBA demonstration for starters :

PHP Code: 

Sub Demo1()
          Const D = "&"" ""&", K = 11
            Dim Rg As Range, V, W, Rc As Range, X, R&
    With Sheet3.[A1].CurrentRegion.Columns
        If Application.CountBlank(.Item(1)) Then
            Set Rg = Sheet4.[A1].CurrentRegion
            V = Rg.Parent.Evaluate(Rg.Columns(10).Address & D & Rg.Columns(K).Address)
            W = .Parent.Evaluate(.Item(10).Address & D & .Item(K).Address)
            Application.ScreenUpdating = False
        For Each Rc In .Item(1).SpecialCells(4)
            X = Application.Match(W(Rc.Row, 1), V, 0)
            If IsNumeric(X) Then R = R + 1: Rg(X, K).ClearContents: W(Rc.Row, 1) = Empty
        Next
        If R Then
            Rg.Sort Rg.Cells(K), 1, Header:=1
            Rg.Parent.Rows(Rg.Rows.Count - R + 1).Resize(R).Delete
        End If
            Application.ScreenUpdating = True
            Set Rg = Nothing
        End If
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄