[已解决]遍历删除问题

张雄友 · 发表于 2014-11-20 22:17

遍历删除问题，出错的。

最佳答案

hwc2ycy

2014-11-20 22:36

Sub 遍历整个工作簿将包含3A的整行删除()
'就是说整个工作簿，只要哪一个单元格包含3A，就将其整行删除。
'Find方法：MatchCase Variant 类型，可选。若为 True，则进行区分大小写的查找。默认值为 False！
Dim c As Range, rng As Range, sh As Worksheet, firstAddress$
For Each sh In Sheets
Set rng = Nothing
With sh.UsedRange
Set c = .Find("3A", , , 1, , , True)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If rng Is Nothing Then Set rng = c.EntireRow Else Set rng = Union(rng, c.EntireRow)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
If Not rng Is Nothing Then
rng.Delete
End If
Next
End Sub

复制代码

hwc2ycy · 发表于 2014-11-20 22:36

Sub 遍历整个工作簿将包含3A的整行删除()
'就是说整个工作簿，只要哪一个单元格包含3A，就将其整行删除。
'Find方法：MatchCase Variant 类型，可选。若为 True，则进行区分大小写的查找。默认值为 False！
Dim c As Range, rng As Range, sh As Worksheet, firstAddress$
For Each sh In Sheets
Set rng = Nothing
With sh.UsedRange
Set c = .Find("3A", , , 1, , , True)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If rng Is Nothing Then Set rng = c.EntireRow Else Set rng = Union(rng, c.EntireRow)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
If Not rng Is Nothing Then
rng.Delete
End If
Next
End Sub

复制代码

Excel学徒123 · 发表于 2014-11-20 22:43

本帖最后由 Excel学徒123 于 2014-11-20 22:44 编辑

复制代码

用的遍历单元格的方法，你看看行不行，没用用FIND{:112:}

qh8600 · 发表于 2014-11-21 08:25

Excel学徒123 发表于 2014-11-20 22:43
用的遍历单元格的方法，你看看行不行，没用用FIND

遍历的话最好不要一行一行的删除和一行一行的写，这样影响速度的

		自动登录	找回密码
密码			注册