Sub renameLvL()

For i = 2 To 3000
    If Cells(i, 2) = "" Then
        Exit For
    End If
    Cells(i, 2) = "~" & Cells(i, 2)
    Cells(i, 3) = Trim(Cells(i, 3))
    
    
    For j = 2 To 14
        Cells(i, j) = Replace(Cells(i, j), vbLf, "")
    Next

Next


For i = 5 To 3000
    If Cells(i, 2) = "" Then
        Exit For
    End If


    For j = 2 To i - 1
        If StrComp(Cells(i, 2), Cells(j, 2)) = 0 Then
            Cells(i, 2) = Cells(i, 2) & "0"
            Exit For
        End If
    Next

Next
End Sub

Sub formatBOM()

    Range("B2:B5000").HorizontalAlignment = xlLeft
    Cells.Select
    Selection.RowHeight = 33
    
    With Selection.Font
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    Range("A1").Select
    
    Call renameLvL
    
    Rows("1:1").RowHeight = 20
    Range("A1") = "prev"
    Range("B1") = "NO."
    Range("D1") = "Rev"
    Columns("A:A").ColumnWidth = 6
    Columns("E:E").ColumnWidth = 20
    Columns("G:K").ColumnWidth = 24
    Columns("B:K").EntireColumn.AutoFit
    Application.PrintCommunication = False
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.05)
        .FooterMargin = Application.InchesToPoints(0.05)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Call formatLvL
    Application.PrintCommunication = True

End Sub


Sub formatLvL()

    [A1].ClearOutline

    ActiveSheet.Outline.SummaryRow = xlSummaryAbove

    For i = 2 To 5000
        If Cells(i, 2) = Empty Then Exit For

    Rows(i).OutlineLevel = count(Cells(i, 2), ".") + 1

    Next

End Sub


Function count(s As String, k As String) As Integer

    count = UBound(Split(s, k))

End Function



Sub main()

    Application.ScreenUpdating = False
    Call formatBOM
    Application.ScreenUpdating = True
    
End Sub