Remove Duplicates in a Dynamic Range using Excel 2007

I am using the follwoing code which works as it is supposed to except that it does not remove the duplicate entries, almost as though the code skips that step. How can I fix this?

Option Explicit
Sub Macro4()
Dim LASTROW As Long
Dim FIRSTROW As Range
Dim SHTWO As Worksheet
Set SHTWO = Sheets("SHEET2")
Dim SHFOUR As Worksheet
Set SHFOUR = Sheets("SHEET4")
Dim RNG As Range
SHTWO.Range("A1:AB5000").ClearContents
SHFOUR.Range("A15:U65536").Copy
SHTWO.Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
Set RNG = Range("U3:U65536")
With RNG
    LASTROW = RNG.Find("*", [U3], xlValues, xlPart, xlByRows, xlPrevious).Row
End With


Dim I As Integer

For I = 3 To LASTROW
    SHTWO.Range(Cells(I, 22), Cells(I, 22)) = Right(SHTWO.Range(Cells(I, 21), Cells(I, 21)), 4)

    SHTWO.Range(Cells(I, 23), Cells(I, 23)) = WorksheetFunction.CountIf(SHTWO.Range(Cells(3, 22), Cells(LASTROW, 22)), Right(SHTWO.Range(Cells(I, 21), Cells(I, 21)), 4))
Next I

SHTWO.Range(Cells(3, 22), Cells(LASTROW, 23)).Copy
SHTWO.Range("AA3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

SHTWO.Range(Cells(3, 27), Cells(LASTROW, 28)).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
SHTWO.Range(Cells(3, 27), Cells(LASTROW, 28)).Sort Key1:=Range("AA3"), Order1:=xlAscending, Header:=xlfalse, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
       









End Sub

Try adding explicit references to your Cell references.

with SHTWO
    .Range(.Cells(3, 27), .Cells(LASTROW, 28)).RemoveDuplicates _
                         Columns:=Array(1, 2), Header:=xlNo
End with

And all the other places in your code where you use CELL without a preceeding dot reference

You need to post example workbook to illustrate your problem

These changes at least get the macro to run.

Sub Macro4()
Dim LASTROW As Long
Dim FIRSTROW As Range
Dim SHTWO As Worksheet
Set SHTWO = Sheets("SHEET2")
Dim SHFOUR As Worksheet
Set SHFOUR = Sheets("SHEET4")
Dim RNG As Range
SHTWO.Range("A1:AB5000").ClearContents
SHFOUR.Range("A15:U65536").Copy
SHTWO.Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
Set RNG = Range("U3:U65536")
With RNG
    LASTROW = RNG.Find("*", [U3], xlValues, xlPart, xlByRows, xlPrevious).Row
End With


Dim I As Integer

For I = 3 To LASTROW
    With SHTWO
        .Range(.Cells(I, 22), .Cells(I, 22)) = Right(.Range(.Cells(I, 21), .Cells(I, 21)), 4)
        .Range(.Cells(I, 23), .Cells(I, 23)) = WorksheetFunction.CountIf(.Range(.Cells(3, 22), .Cells(LASTROW, 22)), Right(.Range(.Cells(I, 21), .Cells(I, 21)), 4))
    End With
Next I

With SHTWO
    .Range(.Cells(3, 22), .Cells(LASTROW, 23)).Copy
    .Range("AA3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

    .Range(.Cells(3, 27), .Cells(LASTROW, 28)).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
    .Range(.Cells(3, 27), .Cells(LASTROW, 28)).Sort Key1:=.Range("AA3"), Order1:=xlAscending, Header:=False, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
       
End With

End Sub

Is your problem still there, as I don't really know what I'm looking for in your data set.