Amazon Competitors Check

Sub Competitors()

'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*UPC:*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*(why?).*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*EAN:*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Sales Rank:*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
        Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Sell Yours*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*See All Product*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Listing Limit*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Hanes*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Champion*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Bali*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Duofold*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Playtex*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*MaidenForm*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Lilyette*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*JMS*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Just my Size*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Red House*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Port*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Sport*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*District*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*CornerStone*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Nike*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*OGIO*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*New Era*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*North Face*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Eddie*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Alternative*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Bella*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Canvas*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Jerzees*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Fruit*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Anvil*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*FOL*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Gildan*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Rabbit Skin*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Russel*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Red Kap*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Bulwark*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:="=*Leggs*" _
        , Operator:=xlAnd
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Columns("A:B").Select
    Selection.Replace What:="ASIN: ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" New & Used Offers", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("A:B").Select
    Selection.Replace What:=" New & Used Offer", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    Selection.End(xlToRight).Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
    Selection.End(xlDown).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 2).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
    Range("D2").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D1").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:D100000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=21"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=21"
    ActiveCell.Offset(0, -2).Select
    ActiveCell.FormulaR1C1 = "=21"
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.FormulaR1C1 = "=21"
    Columns("D:D").Select
    Selection.Find(What:="21", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -2).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "0"
    ActiveCell.Offset(0, -1).Select
    Rows(ActiveCell.Row).Select
    Columns("C:D").Select
    Selection.Delete Shift:=xlToLeft
        Range("A1:B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B1000000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B1000000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    Selection.Find(What:="b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
   Rows(ActiveCell.Row).Select
   Range(Selection, Selection.End(xlDown)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Delete Shift:=xlUp
   Range("A1").Select
  End Sub