Sub AlternatesFormatting()
Application.ScreenUpdating = False
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "STCODE"
Range("B1").Select
ActiveCell.FormulaR1C1 = "DESCRIPTION"
Range("C1").Select
ActiveCell.FormulaR1C1 = "MANU"
Range("D1").Select
ActiveCell.FormulaR1C1 = "SUM"
Range("E1").Select
ActiveCell.FormulaR1C1 = "PRD GRP"
Range("F1").Select
ActiveCell.FormulaR1C1 = "ALT1"
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:T1"), Type:=xlFillDefault
Range("F1:T1").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Cells.Select
Range("A1").Select
ActiveWindow.DisplayGridlines = True
Application.CutCopyMode = False
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
End With
Cells.EntireColumn.AutoFit
Range("A1").Select
Dim LastRowIndex As Integer
Dim RowIndex As Integer
Dim UsedRng As Range
Set UsedRng = ActiveSheet.UsedRange
LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
Application.ScreenUpdating = False
For RowIndex = LastRowIndex To 1 Step -1
If Application.CountA(Rows(RowIndex)) = 0 Then
Rows(RowIndex).Delete
End If
Next RowIndex
Range("A1").Select
Dim lr As Long
Dim rng1 As Range
Dim rng2 As Range
' Find last row with data in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row
' Set ranges
Set rng1 = Range("A2:A" & lr)
Set rng2 = Range("A1:T" & lr)
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 key:=rng1, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange rng2
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Cells.Select
Selection.NumberFormat = "General"
Range("A1").Select
Application.ScreenUpdating = True
End Sub