Private Sub CommandButton1_Click()

   Set a = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants) '這邊是用常數設定(xlCellTypeConstants)取得資料非用可視部分(xlCellTypeConstants)
   
   Total = "" '資料堆壘用
   
   For Each a_out In a '迴圈堆壘資料
        
        If Total = "" Then
        
             Total = a_out.Value
        
        Else
        
             Total = Total & "@" & a_out.Value
        
        End If
   
   Next
   
   Sheets("工作表2").Range("a1:" & "a" & a.Count) = WorksheetFunction.Transpose(Split(Total, "@")) '寫入上資料
   
   
   '排序資料
    Sheets("工作表2").Range("a1:" & "a" & a.Count).Sort Key1:=Sheets("工作表2").Range("a1:" & "a" & a.Count), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
    :=xlStroke, DataOption1:=xlSortNormal
    
   End Sub