- 帖子
- 967
- 主題
- 0
- 精華
- 0
- 積分
- 1001
- 點名
- 0
- 作業系統
- WIN XP
- 軟體版本
- OFFICE 2003
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-11-29
- 最後登錄
- 2022-5-17
 
|
6#
發表於 2012-6-6 10:50
| 只看該作者
本帖最後由 register313 於 2012-6-6 13:04 編輯
回復 5# marklos - Sub xx()
- Sheets("sheet1").Cells.Copy Sheets("sheet2").[A1]
- Sheets("sheet2").Select
- For d = [A1].End(xlDown).Row To 2 Step -1
- c = Application.CountA(Cells(d, 4).Resize(1, 2))
- If Cells(d, 4) <> "" Then
- Rows(d).Copy
- Rows(d + 1).Resize(c).Insert Shift:=xlDown
- Cells(d + 1, 3).Resize(c, 1) = Application.Transpose(Cells(d, 4).Resize(1, 2))
- End If
- Next d
- Columns("d:e").Delete
- End Sub
複製代碼- Sub yy()
- Set d = CreateObject("scripting.dictionary")
- With Sheets("sheet1")
- For i = 2 To .[A1].End(xlDown).Row
- Ar = .Range(.Cells(i, "A"), .Cells(i, "K"))
- d(.Cells(i, 3).Value) = Ar
- For j = 4 To 5
- If .Cells(i, j) <> "" Then
- Ar(1, 3) = .Cells(i, j)
- d(.Cells(i, j).Value) = Ar
- End If
- Next j
- Next i
- End With
- With Sheets("sheet2")
- .Cells = ""
- Sheets("sheet1").Rows(1).Copy .[A1]
- .[A2].Resize(d.Count, 11) = Application.Transpose(Application.Transpose(d.items))
- .Columns("D:E").Delete
- .[A1].CurrentRegion.Borders.LineStyle = xlContinuous
- End With
- End Sub
複製代碼 |
|