[SOLVED] (thanks to jaslake and ChemistB) Deleting Duplicate Values/Rows???

[clintandcaden]

How would i delete duplicate values which consist of a row without deleting the blank rows and formating of my rows?
Using the Delete duplicate, it deletes the duplicates (great) but then it also delets the rest of my sheet that was formated with certain colors, etc
Any help/suggestions would be greatly appreciated!
Thanks,
Clint

[ChemistB]

On your "Job List Tracker" sheet, there don't seem to be any duplicates (I was looking at W/O#'s). So you want to clear contents rather than delete?

[clintandcaden]

On your "Job List Tracker" sheet, there don't seem to be any duplicates (I was looking at W/O#'s). So you want to clear contents rather than delete?

Thnkyou for replying. You have to run the Macro titled WO_Numbers. That will copy all the jobs that are ready to schedule into sheet 2 but if i were to add more data onto sheet1 the next day and then run the macro again it copys the old ones and the new ones to sheet2, therefore creating duplicates onto sheet2. Sorry i didn't explain further in my first post. Yes clearing the contents on sheet2 will work, but i changed it on mine (not on the one i uploaded) excel was counting the color as a value, thus deleting it. so i just took the colors off the second sheet. If i were able to keep the colors then that would be great, but thats not my main concern now. Now it would be as I stated above. Possibly Making the Macro not copy the ones it has already put on sheet2 somehow if possible...

[jaslake]

Hi clintandcaden

This code is in the attached

Option Explicit

Sub test()
    Dim LR As Long
    Dim LC As Long
    Application.ScreenUpdating = False
    With Sheets("JOB LIST TRACKER").ListObjects("Table159")
        .Range.AutoFilter Field:=7, Criteria1:="Y"
        .Range.AutoFilter Field:=9, Criteria1:="<>X"
        .AutoFilter.Range.Offset(1, 0).Copy
    End With

    With Sheets("Ready To Schedule")
        LR = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        .Range("A" & LR).PasteSpecial
    End With

    With Sheets("JOB LIST TRACKER")
        LR = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        .Range("I5:I" & LR).Value = "X"
        .ShowAllData
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

...it appears to do as you require. Click the Button to run. Let me know of issues.

[clintandcaden]

Wow thanks!! that worked perfectly from what i can tell on this school computer. When i get to work in the morning i will double check but so far so good. You have saved my brain alot of trouble. I will change this post to SOLVED after i double check in the morning.
THANKYOU jaslake!

[clintandcaden]

thanks for the help jaslake, I am getting Run Time Error '1004'

Option Explicit

Sub test()
    Dim LR As Long
    Dim LC As Long
    Application.ScreenUpdating = False
    With Sheets("JOB LIST TRACKER").ListObjects("Table159")
        .Range.AutoFilter Field:=7, Criteria1:="Y"
        .Range.AutoFilter Field:=9, Criteria1:="<>X"
        .AutoFilter.Range.Offset(1, 0).Copy
    End With

    With Sheets("Ready To Schedule")
        LR = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        .Range("A" & LR).PasteSpecial
    End With

    With Sheets("JOB LIST TRACKER")
        LR = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        .Range("I5:I" & LR).Value = "X"
        .ShowAllData
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

The error is at the .ShowAllData


AND UPON FURTHER INSPECTION THE MORE I RUN AND TEST IT THE ERROR GOES AWAY. BUT THE INITIAL OPENING AND RUNNING OF IT GIVES THAT ERROR

[ChemistB]

Hi Clint,
You need to add a column "X" in Col I. Jaslake's macro uses that to track which W/O's are in sheet 2. Take a look at his upload in post 4
.

[clintandcaden]

thank both of you for the help. It is greatly appreciated

[jaslake]

Hi clintandcaden

You're welcome...glad I could help.

@ChemistB
Thanks for explaining about Header I...I should have mentioned that and failed to.