- 帖子
- 1018
- 主題
- 15
- 精華
- 0
- 積分
- 1058
- 點名
- 0
- 作業系統
- win7 32bit
- 軟體版本
- Office 2016 64-bit
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 桃園
- 註冊時間
- 2012-5-9
- 最後登錄
- 2022-9-28
|
2#
發表於 2013-6-7 16:28
| 只看該作者
本帖最後由 stillfish00 於 2013-6-7 16:38 編輯
回復 1# luke
土法煉鋼，參考看看
其實跟有沒有合併欄好像沒啥關係- Sub test()
- Dim r As Long, i As Long, ar
- Dim rngDelete As Range
-
- With Sheets("sheet1")
- '先整理A~E
- r = Application.WorksheetFunction.Max( _
- .Cells(.Rows.Count, "A").End(xlUp).Row, _
- .Cells(.Rows.Count, "B").End(xlUp).Row, _
- .Cells(.Rows.Count, "E").End(xlUp).Row)
- With .Range("A1:E1").Resize(r)
- ar = .Value
- For i = 1 To UBound(ar)
- If ar(i, 1) = "" Then
- If rngDelete Is Nothing Then
- Set rngDelete = .Cells(i, 1).Resize(, .Columns.Count)
- Else
- Set rngDelete = Union(rngDelete, .Cells(i, 1).Resize(, .Columns.Count))
- End If
- End If
- Next
- If Not rngDelete Is Nothing Then
- rngDelete.Delete Shift:=xlUp
- Set rngDelete = Nothing
- End If
- End With
- '同樣步驟，整理F~J
- r = Application.WorksheetFunction.Max( _
- .Cells(.Rows.Count, "F").End(xlUp).Row, _
- .Cells(.Rows.Count, "G").End(xlUp).Row, _
- .Cells(.Rows.Count, "J").End(xlUp).Row)
- With .Range("F1:J1").Resize(r)
- ar = .Value
- For i = 1 To UBound(ar)
- If ar(i, 1) = "" Then
- If rngDelete Is Nothing Then
- Set rngDelete = .Cells(i, 1).Resize(, .Columns.Count)
- Else
- Set rngDelete = Union(rngDelete, .Cells(i, 1).Resize(, .Columns.Count))
- End If
- End If
- Next
- If Not rngDelete Is Nothing Then
- rngDelete.Delete Shift:=xlUp
- Set rngDelete = Nothing
- End If
- End With
- End With
-
- End Sub
|
|
發表於 2013-6-7 18:56
| 
