[發問] 想請問兩列其中有亂數空白格問題

albertbug

各位好
我想請問  我有些數字想處理
甲  乙  排列好
          5
5   8    8
9         9
4   5    4
          5
          3
3   5    5

如上圖  有些位置是空格  但是我要根據A1 B1 A2 B2 A3 B3
一起排列在C那邊(空格不排列)

這樣應該要怎麼做比較方便  我有傳附件  再麻煩各位大大了
感謝

GBKEE

回復 1# albertbug

1. Sub Ex()
   
2.     Dim D As Object, E As Range
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     For Each E In Range("A2:B30")
   
5.         If E <> "" Then D(E) = E.Value
   
6.     Next
   
7.     [D2:D65536] = ""
   
8.     [D2].Resize(D.Count) = Application.Transpose(D.ITEMS)
   
9. End Sub

複製代碼

Hsieh

1. Sub Ex()
   
2. Dim Ay()
   
3. ar = [A1].Resize(ActiveSheet.UsedRange.Rows.Count, 2)
   
4. For i = 2 To UBound(ar, 1)
   
5.    For j = 1 To 2
   
6.       If ar(i, j) <> "" Then
   
7.          ReDim Preserve Ay(s)
   
8.          Ay(s) = ar(i, j)
   
9.          s = s + 1
   
10.       End If
    
11.    Next
    
12. Next
    
13. [D1:D65536] = ""
    
14. [D2].Resize(s, 1) = Application.Transpose(Ay)
    
15. End Sub

複製代碼

回復 1# albertbug

albertbug

感謝GBKEE & Hsieh的教導感謝
但是可以在問個很蠢的問題
如何執行EX()
是利用新增表單Private Sub CommandButton1_Click()
去Call ex()嗎

還是要如何執行  XD  感謝  希望大大再教導

GBKEE

回復 4# albertbug

1. Private Sub CommandButton1_Click()
   
2.     Dim D As Object, E As Range
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     For Each E In Range("A2:B30")
   
5.         If E <> "" Then D(E) = E.Value
   
6.     Next
   
7.     [D2:D65536] = ""
   
8.     [D2].Resize(D.Count) = Application.Transpose(D.ITEMS)
   
9. End Sub

複製代碼

albertbug

感謝GBKEE大大