every time I run this code the auto filter only filters 1 column any ideals

A

alan myers

Board Regular
Joined
Oct 31, 2017
Messages
119
Office Version
  1. 365
Platform
  1. Windows
VBA Code: 
Sub FRANCHISEBALL()



'*************************
' color background
'*************************'

    Cells.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With


'*************************
' rename hearders
'*************************
  
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Main/RLINK"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "NAME"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "TEAM"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "THROWS"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "SEASON/AB"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "HITS"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "RBI"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "SLG"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "2B"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "3B"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "HR"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "BB"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "RUNS"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "TB"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "SB"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "SB%"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "OBP"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "OPS"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "XBH"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "AB/HR"
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "ISO"
    Range("AE1").Select
    ActiveCell.FormulaR1C1 = "RC"
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "E"
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "CAREER/SEASONS"
    Range("AH1").Select
    ActiveCell.FormulaR1C1 = "TEAM"
    Range("AI1").Select
    ActiveCell.FormulaR1C1 = "AB"
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "HITS"
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("AL1").Select
    ActiveCell.FormulaR1C1 = "RBI"
    Range("AM1").Select
    ActiveCell.FormulaR1C1 = "SLG"
    Range("AN1").Select
    ActiveCell.FormulaR1C1 = "2B"
    Range("AO1").Select
    ActiveCell.FormulaR1C1 = "3B"
    Range("AP1").Select
    ActiveCell.FormulaR1C1 = "HR"
    Range("AQ1").Select
    ActiveCell.FormulaR1C1 = "SB"
    Range("AR1").Select
    ActiveCell.FormulaR1C1 = "MINORS/AB"
    Range("AS1").Select
    ActiveCell.FormulaR1C1 = "HITS"
    Range("AT1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("AU1").Select
    ActiveCell.FormulaR1C1 = "RBI"
    Range("AV1").Select
    ActiveCell.FormulaR1C1 = "SLG"
    Range("AW1").Select
    ActiveCell.FormulaR1C1 = "2B"
    Range("AX1").Select
    ActiveCell.FormulaR1C1 = "3B"
    Range("AY1").Select
    ActiveCell.FormulaR1C1 = "HR"
    Range("AZ1").Select
    ActiveCell.FormulaR1C1 = "BB"
    Range("BA1").Select
    ActiveCell.FormulaR1C1 = "TB"
    Range("BB1").Select
    ActiveCell.FormulaR1C1 = "PA"
    Range("BC1").Select
    ActiveCell.FormulaR1C1 = "OBP"
    Range("BD1").Select
    ActiveCell.FormulaR1C1 = "OPS"
    Range("BE1").Select
    ActiveCell.FormulaR1C1 = "BB/PA"
    Range("BF1").Select
    ActiveCell.FormulaR1C1 = "XBH"
    Range("BG1").Select
    ActiveCell.FormulaR1C1 = "AB/HR"
    Range("BH1").Select
    ActiveCell.FormulaR1C1 = "ISO"
    Range("BI1").Select
    ActiveCell.FormulaR1C1 = "RC"
    Range("BJ1").Select
    ActiveCell.FormulaR1C1 = "SCOUT PIT/CONTROL"
    Range("BK1").Select
    ActiveCell.FormulaR1C1 = "MOVEMENT"
    Range("BH8:BJ16").Select
    Range("BL1").Select
    ActiveCell.FormulaR1C1 = "VELOCITY"
    Range("BM1").Select
    ActiveCell.FormulaR1C1 = "STAMINA"
    Range("BN1").Select
    ActiveCell.FormulaR1C1 = "POWER"
    Range("BO1").Select
    ActiveCell.FormulaR1C1 = "SCOUTH-CONTACT"
    Range("BN1").Select
    ActiveCell.FormulaR1C1 = "SCOUT HIT/POWER"
    Range("BO1").Select
    ActiveCell.FormulaR1C1 = "CONTACT"
    Range("BP1").Select
    ActiveCell.FormulaR1C1 = "SPEED"
    Range("BQ1").Select
    ActiveCell.FormulaR1C1 = "DEFENSE"
    Range("BR1").Select
    ActiveCell.FormulaR1C1 = "SCOUT RANGE/LF"
    Range("BS1").Select
    ActiveCell.FormulaR1C1 = "LC"
    Range("BT1").Select
    ActiveCell.FormulaR1C1 = "C"
    Range("BU1").Select
    ActiveCell.FormulaR1C1 = "RC"
    Range("BV1").Select
    ActiveCell.FormulaR1C1 = "RF"
    Range("BV2").Select


'*************************
' format cells
'*************************

    Cells.Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 2
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


'*************************
' MOVE NAMES COLUMN
'*************************

    Columns("B:B").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-1],RC[-2])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C87354")
    Range("C2:C87354").Select



'*************************
' COLOR NAMES
'*************************

    Columns("C:C").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


'*************************
' HIDE ROWS
'*************************

    Columns("A:B").Select
    Range("B1").Activate
    Selection.EntireColumn.Hidden = True


'*************************
' color hearders
'*************************

    Range("A1:M1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Range("N1:AG1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
 
    Range("AH1:AR1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Range("AS1:BJ1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
  
    Range("BK1:BN1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BO1:BR1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BS1:BW1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

  
'*************************
' CHANGE NAME HEADER COLOR
'*************************'

    Range("C1").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""NAME"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


'*************************
' FORMAT NUMBERS
'*************************

    Columns("F:F").Select
    Selection.NumberFormat = "0"
    Columns("G:G").Select
    Selection.NumberFormat = "0"
    Columns("J:J").Select
    Selection.NumberFormat = "0.000"
    Columns("K:K").Select
    Selection.NumberFormat = "0.000"
    Columns("L:L").Select
    Selection.NumberFormat = "0"
    Columns("N:N").Select
    Selection.NumberFormat = "0"
    Columns("O:O").Select
    Selection.NumberFormat = "0"
    Columns("P:P").Select
    Selection.NumberFormat = "0.000"
    Columns("Q:Q").Select
    Selection.NumberFormat = "0"
    Columns("R:R").Select
    Selection.NumberFormat = "0.000"
    Columns("S:S").Select
    Selection.NumberFormat = "0"
    Columns("T:T").Select
    Selection.NumberFormat = "0"
    Columns("U:U").Select
    Selection.NumberFormat = "0"
    Columns("V:V").Select
    Selection.NumberFormat = "0"
    Columns("W:W").Select
    Selection.NumberFormat = "0"
    Columns("X:X").Select
    Selection.NumberFormat = "0"
    Columns("Y:Y").Select
    Selection.NumberFormat = "0"
    Columns("Z:Z").Select
    Selection.NumberFormat = "0"
    Columns("AA:AA").Select
    Selection.NumberFormat = "0.000"
    Columns("AB:AB").Select
    Selection.NumberFormat = "0.000"
    Columns("AC:AC").Select
    Selection.NumberFormat = "0"
    Columns("AD:AD").Select
    Selection.NumberFormat = "0.00"
    Columns("AE:AE").Select
    Selection.NumberFormat = "0.000"
    Columns("AF:AF").Select
    Selection.NumberFormat = "0.00"
    Columns("AG:AG").Select
    Selection.NumberFormat = "0"
    Columns("AH:AH").Select
    Selection.NumberFormat = "0"
    Columns("AJ:AJ").Select
    Selection.NumberFormat = "0"
    Columns("AK:AK").Select
    Selection.NumberFormat = "0"
    Columns("AL:AL").Select
    Selection.NumberFormat = "0.000"
    Columns("AM:AM").Select
    Selection.NumberFormat = "0"
    Columns("AN:AN").Select
    Selection.NumberFormat = "0.000"
    Columns("AO:AO").Select
    Selection.NumberFormat = "0"
    Columns("AP:AP").Select
    Selection.NumberFormat = "0"
    Columns("AQ:AQ").Select
    Selection.NumberFormat = "0"
    Columns("AR:AR").Select
    Selection.NumberFormat = "0"
    Columns("AS:AS").Select
    Selection.NumberFormat = "0"
    Columns("AT:AT").Select
    Selection.NumberFormat = "0"
    Columns("AU:AU").Select
    Selection.NumberFormat = "0.000"
    Columns("AV:AV").Select
    Selection.NumberFormat = "0"
    Columns("AW:AW").Select
    Selection.NumberFormat = "0.000"
    Columns("AX:AX").Select
    Selection.NumberFormat = "0"
    Columns("AY:AY").Select
    Selection.NumberFormat = "0"
    Columns("AZ:AZ").Select
    Selection.NumberFormat = "0"
    Columns("BA:BA").Select
    Selection.NumberFormat = "0"
    Columns("BB:BB").Select
    Selection.NumberFormat = "0"
    Columns("BC:BC").Select
    Selection.NumberFormat = "0"
    Columns("BD:BD").Select
    Selection.NumberFormat = "0.000"
    Columns("BE:BE").Select
    Selection.NumberFormat = "0.000"
    Columns("BF:BF").Select
    Selection.NumberFormat = "0.000"
    Columns("BG:BG").Select
    Selection.NumberFormat = "0"
    Columns("BH:BH").Select
    Selection.NumberFormat = "0.00"
    Columns("BI:BI").Select
    Selection.NumberFormat = "0.000"
    Columns("BJ:BJ").Select
    Selection.NumberFormat = "0.00"
    Columns("BK:BK").Select
    Selection.NumberFormat = "0"
    Columns("BL:BL").Select
    Selection.NumberFormat = "0"
    Columns("BM:BM").Select
    Selection.NumberFormat = "0"
    Columns("BN:BN").Select
    Selection.NumberFormat = "0"
    Columns("BO:BO").Select
    Selection.NumberFormat = "0"
    Columns("BP:BP").Select
    Selection.NumberFormat = "0"
    Columns("BQ:BQ").Select
    Selection.NumberFormat = "0"
    Columns("BR:BR").Select
    Selection.NumberFormat = "0"
    Columns("BS:BS").Select
    Selection.NumberFormat = "0"
    Columns("BT:BT").Select
    Selection.NumberFormat = "0"
    Columns("BU:BU").Select
    Selection.NumberFormat = "0"
    Columns("BV:BV").Select
    Selection.NumberFormat = "0"
    Columns("BW:BW").Select
    Selection.NumberFormat = "0"


'***************************
' FREEZ FRAME
'***************************

    Range("D2").Select
    With ActiveWindow
        .SplitColumn = 1
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True


'*************************
' TEXXT TO COLUMN
'*************************

    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("J:J").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("K:K").Select
    Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("L:L").Select
    Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("N:N").Select
    Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("O:O").Select
    Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Q:Q").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("R:R").Select
    Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("S:S").Select
    Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("T:T").Select
    Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("U:U").Select
    Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("V:V").Select
    Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("W:W").Select
    Selection.TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("X:X").Select
    Selection.TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Y:Y").Select
    Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 
    Columns("Z:Z").Select
    Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AA:AA").Select
    Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AB:AB").Select
    Selection.TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AC:AC").Select
    Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AD:AD").Select
    Selection.TextToColumns Destination:=Range("AD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AE:AE").Select
    Selection.TextToColumns Destination:=Range("AE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AF:AF").Select
    Selection.TextToColumns Destination:=Range("AF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AG:AG").Select
    Selection.TextToColumns Destination:=Range("AG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AH:AH").Select
    Selection.TextToColumns Destination:=Range("AH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AJ:AJ").Select
    Selection.TextToColumns Destination:=Range("AJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AK:AK").Select
    Selection.TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AL:AL").Select
    Selection.TextToColumns Destination:=Range("AL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AM:AM").Select
    Selection.TextToColumns Destination:=Range("AM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AN:AN").Select
    Selection.TextToColumns Destination:=Range("AN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AO:AO").Select
    Selection.TextToColumns Destination:=Range("AO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AP:AP").Select
    Selection.TextToColumns Destination:=Range("AP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AQ:AQ").Select
    Selection.TextToColumns Destination:=Range("AQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AR:AR").Select
    Selection.TextToColumns Destination:=Range("AR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AS:AS").Select
    Selection.TextToColumns Destination:=Range("AS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AT:AT").Select
    Selection.TextToColumns Destination:=Range("AT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AU:AU").Select
    Selection.TextToColumns Destination:=Range("AU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AV:AV").Select
    Selection.TextToColumns Destination:=Range("AV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AW:AW").Select
    Selection.TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AX:AX").Select
    Selection.TextToColumns Destination:=Range("AX1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AY:AY").Select
    Selection.TextToColumns Destination:=Range("AY1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AZ:AZ").Select
    Selection.TextToColumns Destination:=Range("AZ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BA:BA").Select
    Selection.TextToColumns Destination:=Range("BA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BB:BB").Select
    Selection.TextToColumns Destination:=Range("BB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BC:BC").Select
    Selection.TextToColumns Destination:=Range("BC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BD:BD").Select
    Selection.TextToColumns Destination:=Range("BD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BE:BE").Select
    Selection.TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BE:BE").Select
    Selection.TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BF:BF").Select
    Selection.TextToColumns Destination:=Range("BF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BG:BG").Select
    Selection.TextToColumns Destination:=Range("BG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BH:BH").Select
    Selection.TextToColumns Destination:=Range("BH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BI:BI").Select
    Selection.TextToColumns Destination:=Range("BI1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BJ:BJ").Select
    Selection.TextToColumns Destination:=Range("BJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BK:BK").Select
    Selection.TextToColumns Destination:=Range("BK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BL:BL").Select
    Selection.TextToColumns Destination:=Range("BL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BM:BM").Select
    Selection.TextToColumns Destination:=Range("BM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BN:BN").Select
    Selection.TextToColumns Destination:=Range("BN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BO:BO").Select
    Selection.TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BP:BP").Select
    Selection.TextToColumns Destination:=Range("BP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BQ:BQ").Select
    Selection.TextToColumns Destination:=Range("BQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BR:BR").Select
    Selection.TextToColumns Destination:=Range("BR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BS:BS").Select
    Selection.TextToColumns Destination:=Range("BS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BT:BT").Select
    Selection.TextToColumns Destination:=Range("BT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BU:BU").Select
    Selection.TextToColumns Destination:=Range("BU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BV:BV").Select
    Selection.TextToColumns Destination:=Range("BV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BW:BW").Select
    Selection.TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

  
'*************************
' auto filter
'*************************

    Selection.AutoFilter
  
  
'*************************
' spacing
'*************************

'    Selection.ColumnWidth = 32.82
    Selection.ColumnWidth = 50.36
    Cells.EntireColumn.AutoFit


'*************************
' FORMATTING SEARCH
'*************************

    Columns("BK:BN").Select
    Range("BN1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=89"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("BO:BR").Select
    Range("BR1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=89"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("BS:BW").Select
    Range("BW1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=5"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
  


End Sub
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hi alan myers,

relevant part of code

Rich (BB code): 
    Columns("BW:BW").Select
    Selection.TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

  
'*************************
' auto filter
'*************************

    Selection.AutoFilter

The last Selection was for Column BW, so any new commands will rely on that range.

The Filter command could be changed to

VBA Code: 
    'set AutoFilter for all cells in Row 1 until last filled cell from the left
    Range("A1", Cells(1, Columns.Count).End(xlToLeft)).AutoFilter

Please find a version without Select, Selection or Activate here (not tested):

VBA Code: 
Sub FRANCHISEBALL()
' https://www.mrexcel.com/board/threads/every-time-i-run-this-code-the-auto-filter-only-filters-1-column-any-ideals.1224315/
' Updated: 20221210
' By:      HaHoBe
' Reason:  Reworked Code from Macro Recorder

'works on the ActiveSheet

Application.ScreenUpdating = False

'*************************
' color background
'*************************'
'*************************
' format cells  <--- moved and merged
'*************************

  With Cells
    With .Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    With .Font
      .ThemeColor = xlThemeColorDark1
      .TintAndShade = 0
    End With
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 2
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With

'*************************
' rename hearders
'*************************
  
    Range("A1").Value = "Main/RLINK"
    Range("B1").Value = "NAME"
    Range("C1").Value = "TEAM"
    Range("G1").Value = "THROWS"
    Range("M1").Value = "SEASON/AB"
    Range("N1").Value = "HITS"
    Range("O1").Value = "AVG"
    Range("P1").Value = "RBI"
    Range("Q1").Value = "SLG"
    Range("R1").Value = "2B"
    Range("S1").Value = "3B"
    Range("T1").Value = "HR"
    Range("U1").Value = "BB"
    Range("V1").Value = "RUNS"
    Range("W1").Value = "TB"
    Range("X1").Value = "SB"
    Range("Y1").Value = "SB%"
    Range("Z1").Value = "OBP"
    Range("AA1").Value = "OPS"
    Range("AB1").Value = "XBH"
    Range("AC1").Value = "AB/HR"
    Range("AD1").Value = "ISO"
    Range("AE1").Value = "RC"
    Range("AF1").Value = "E"
    Range("AG1").Value = "CAREER/SEASONS"
    Range("AH1").Value = "TEAM"
    Range("AI1").Value = "AB"
    Range("AJ1").Value = "HITS"
    Range("AK1").Value = "AVG"
    Range("AL1").Value = "RBI"
    Range("AM1").Value = "SLG"
    Range("AN1").Value = "2B"
    Range("AO1").Value = "3B"
    Range("AP1").Value = "HR"
    Range("AQ1").Value = "SB"
    Range("AR1").Value = "MINORS/AB"
    Range("AS1").Value = "HITS"
    Range("AT1").Value = "AVG"
    Range("AU1").Value = "RBI"
    Range("AV1").Value = "SLG"
    Range("AW1").Value = "2B"
    Range("AX1").Value = "3B"
    Range("AY1").Value = "HR"
    Range("AZ1").Value = "BB"
    Range("BA1").Value = "TB"
    Range("BB1").Value = "PA"
    Range("BC1").Value = "OBP"
    Range("BD1").Value = "OPS"
    Range("BE1").Value = "BB/PA"
    Range("BF1").Value = "XBH"
    Range("BG1").Value = "AB/HR"
    Range("BH1").Value = "ISO"
    Range("BI1").Value = "RC"
    Range("BJ1").Value = "SCOUT PIT/CONTROL"
    Range("BK1").Value = "MOVEMENT"
    Range("BL1").Value = "VELOCITY"
    Range("BM1").Value = "STAMINA"
    Range("BN1").Value = "POWER"
    Range("BO1").Value = "SCOUTH-CONTACT"
    Range("BN1").Value = "SCOUT HIT/POWER"
    Range("BO1").Value = "CONTACT"
    Range("BP1").Value = "SPEED"
    Range("BQ1").Value = "DEFENSE"
    Range("BR1").Value = "SCOUT RANGE/LF"
    Range("BS1").Value = "LC"
    Range("BT1").Value = "C"
    Range("BU1").Value = "RC"
    Range("BV1").Value = "RF"


'*************************
' MOVE NAMES COLUMN
'*************************

    Columns("B:B").Copy
    Columns("A:A").Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("C2").FormulaR1C1 = "=HYPERLINK(RC[-1],RC[-2])"
    Range("C2").AutoFill Destination:=Range("C2:C87354")


'*************************
' COLOR NAMES
'*************************

    With Columns("C:C").Select
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With

'*************************
' HIDE ROWS   <-- Comment should otell what you do, next command is hiding Columns
'*************************

    Columns("A:B").EntireColumn.Hidden = True


'*************************
' color hearders
'*************************

    With Range("A1:M1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
 
    withRange ("N1:AG1")
      With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      With .Font
          .ColorIndex = xlAutomatic
          .TintAndShade = 0
      End With
    End With
 
    With Range("AH1:AR1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 5287936
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
 
    With Range("AS1:BJ1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 12611584
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  
    With Range("BK1:BN1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 255
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
    
    With Range("BO1:BR1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 49407
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
    
    With Range("BS1:BW1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

  
'*************************
' CHANGE NAME HEADER COLOR
'*************************'

    With Range("C1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""NAME"""
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With

'*************************
' FORMAT NUMBERS
'*************************
    
    
    Range("F:G,L:L,N:O,Q:Q,S:Z,AC:AC,AG:AH,AJ:AK,AM:AM,AO:AT,AV:AV,AX:BC,BG:BG,BK:BW").NumberFormat = "0"
    Range("J:K,P:P,R:R,AA:AB,AD:AE,AL:AL,AN:AN,AU:AU,AW:AW,BD:BF,BI:BI").NumberFormat = "0.000"
    Range("AF:AF,BH:BH,BJ:BJ").NumberFormat = "0.00"
    
'    Columns("F:G").NumberFormat = "0"
'    Columns("J:K").NumberFormat = "0.000"
'    Columns("L:L").NumberFormat = "0"
'    Columns("N:O").NumberFormat = "0"
'    Columns("P:P").NumberFormat = "0.000"
'    Columns("Q:Q").NumberFormat = "0"
'    Columns("R:R").NumberFormat = "0.000"
'    Columns("S:Z").NumberFormat = "0"
'    Columns("AA:AB").NumberFormat = "0.000"
'    Columns("AC:AC").NumberFormat = "0"
'    Columns("AD:AE").NumberFormat = "0.000"
'    Columns("AF:AF").NumberFormat = "0.00"
'    Columns("AG:AH").NumberFormat = "0"
'    Columns("AJ:AK").NumberFormat = "0"
'    Columns("AL:AL").NumberFormat = "0.000"
'    Columns("AM:AM").NumberFormat = "0"
'    Columns("AN:AN").NumberFormat = "0.000"
'    Columns("AO:AT").NumberFormat = "0"
'    Columns("AU:AU").NumberFormat = "0.000"
'    Columns("AV:AV").NumberFormat = "0"
'    Columns("AW:AW").NumberFormat = "0.000"
'    Columns("AX:BC").NumberFormat = "0"
'    Columns("BD:BF").NumberFormat = "0.000"
'    Columns("BG:BG").NumberFormat = "0"
'    Columns("BH:BH").NumberFormat = "0.00"
'    Columns("BI:BI").NumberFormat = "0.000"
'    Columns("BJ:BJ").NumberFormat = "0.00"
'    Columns("BK:BW").NumberFormat = "0"


'***************************
' FREEZ FRAME
'***************************

    With ActiveWindow
      .SplitColumn = 1
      .SplitRow = 1
      .FreezePanes = True
      .FreezePanes = False
      .FreezePanes = True
    End With


'*************************
' TEXXT TO COLUMN
'*************************

    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("L:L").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("O:O").TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("P:P").TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Q:Q").TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("R:R").TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("S:S").TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("T:T").TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("U:U").TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("V:V").TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("W:W").TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("X:X").TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Y:Y").TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 
    Columns("Z:Z").TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AA:AA").TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AB:AB").TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AC:AC").TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AD:AD").TextToColumns Destination:=Range("AD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AE:AE").TextToColumns Destination:=Range("AE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AF:AF").TextToColumns Destination:=Range("AF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AG:AG").TextToColumns Destination:=Range("AG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AH:AH").TextToColumns Destination:=Range("AH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AJ:AJ").TextToColumns Destination:=Range("AJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AK:AK").TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AL:AL").TextToColumns Destination:=Range("AL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AM:AM").TextToColumns Destination:=Range("AM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AN:AN").TextToColumns Destination:=Range("AN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AO:AO").TextToColumns Destination:=Range("AO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AP:AP").TextToColumns Destination:=Range("AP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AQ:AQ").TextToColumns Destination:=Range("AQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AR:AR").TextToColumns Destination:=Range("AR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AS:AS").TextToColumns Destination:=Range("AS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AT:AT").TextToColumns Destination:=Range("AT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AU:AU").TextToColumns Destination:=Range("AU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AV:AV").TextToColumns Destination:=Range("AV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AW:AW").TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AX:AX").TextToColumns Destination:=Range("AX1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AY:AY").TextToColumns Destination:=Range("AY1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AZ:AZ").TextToColumns Destination:=Range("AZ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BA:BA").TextToColumns Destination:=Range("BA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BB:BB").TextToColumns Destination:=Range("BB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BC:BC").TextToColumns Destination:=Range("BC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BD:BD").TextToColumns Destination:=Range("BD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BE:BE").TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BF:BF").TextToColumns Destination:=Range("BF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BG:BG").TextToColumns Destination:=Range("BG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BH:BH").TextToColumns Destination:=Range("BH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BI:BI").TextToColumns Destination:=Range("BI1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BJ:BJ").TextToColumns Destination:=Range("BJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BK:BK").TextToColumns Destination:=Range("BK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BL:BL").TextToColumns Destination:=Range("BL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BM:BM").TextToColumns Destination:=Range("BM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BN:BN").TextToColumns Destination:=Range("BN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BO:BO").TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BP:BP").TextToColumns Destination:=Range("BP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BQ:BQ").TextToColumns Destination:=Range("BQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BR:BR").TextToColumns Destination:=Range("BR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BS:BS").TextToColumns Destination:=Range("BS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BT:BT").TextToColumns Destination:=Range("BT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BU:BU").TextToColumns Destination:=Range("BU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BV:BV").TextToColumns Destination:=Range("BV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BW:BW").TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

  
'*************************
' auto filter
'*************************

    'disable any AutoFilter
    ActiveSheet.AutoFilterMode = False
0
  
'*************************
' spacing
'*************************

'    Selection.ColumnWidth = 32.82
'    Selection.ColumnWidth = 50.36
    Cells.EntireColumn.AutoFit


'*************************
' FORMATTING SEARCH
'*************************

    With Range("BN1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=89"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
      End With
      With .FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End If
    
    With Range("BR1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=89"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
      End With
      With .FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With
    
    With Range("BW1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=5"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
      End With
      With .FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With

    Application.ScreenUpdating = True

End Sub

Ciao,
Holger
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

D
  • Question
VBA optimization advice
Replies
3
Views
139
bobsan42
bobsan42
A
  • Solved
Looping Through Columns
Replies
3
Views
524
kevin9999
K
J
  • Question
Automatically import .txt files into separate sheets in Excel
Replies
1
Views
222
John_w
J
I
  • Solved
Moving Data based off a condition
Replies
7
Views
174
ItalianPlatinum
I
Q
  • Question
How to remove 400 error from clear even rows/text to columns macro?
Replies
1
Views
164
Joe4
Joe4

Forum statistics

Threads
1,215,371
Messages
6,124,529
Members
449,169
Latest member
mm424

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top