[EXCEL]巨集範例

Sub HCUP160P_AUTO()

'

' HCUP160P_AUTO 巨集

'

'

    Rows("1:19").Select ' 選取1-19row

    Range("A19").Activate

    Selection.Delete Shift:=xlUp '刪除選取的row

    

    

    Columns("A:AE").Select

    With ActiveWindow

        .SplitColumn = 0

        .SplitRow = 0

    End With

    Selection.UnMerge

    

    Cells.Select

    With Selection

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlLTR

        .MergeCells = False

    End With

    Rows("1:1").Select

    With ActiveWindow

        .SplitColumn = 0

        .SplitRow = 1

    End With

    ActiveWindow.FreezePanes = True

    

    

    Dim i As Long

    i = ActiveSheet.UsedRange.Rows.Count '取得現在工作表有幾個row

    Dim strD As String

    strD = "Z" + Str(i)

    strD = Replace(strD, " ", "")  '算出右下角最後一格

    

    '依照G欄做排序

    Columns("G:G").Select

    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear

    ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add Key:=Range("G1"), _

        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort

        .SetRange Range("A2:" + strD) '排序範圍擴大

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Columns("A:Z").EntireColumn.AutoFit '完成後自動欄寬

End Sub