Question

Excel，宏删除带某关键字的行，求帮助

我的表中，A列是一些城市名单，我每次都要从中删除掉几个城市，我从网上改编了一段程序，如下：

Sub 去除2()
Dim A As String
Dim AA

For Each AA In Columns("A:A").Cells

If AA Like "北京" Or AA Like "成都" Or AA Like "赤峰" Or AA Like "杭州" Or AA Like "上海" Or AA Like "深圳" Or AA Like "沈阳" Or AA Like "石家" Or AA Like "潍坊" Or AA Like "无锡" Or AA Like "武汉" Or AA Like "郑州" Or AA Like "总计" Then
AA.Activate
ActiveCell.EntireRow.Delete
End If
Next
End Sub

该段程序能将这些城市删除，但是问题是，删完了之后，程序还要响应半天才能结束，我感觉是不是这段程序中有一些不必要的东西可以改改，谁能帮我优化一下，谢谢！

Answer 1

可以做如下修改：（1）删除的时候，直接删除，而不实先选定；（2）屏幕不闪动
如下：

Sub 去除2()
Dim A As String
Dim AA

Application.ScreenUpdating = False

For Each AA In Columns("A:A").Cells

If AA Like "北京" Or AA Like "成都" Or AA Like "赤峰" Or AA Like "杭州" Or AA Like "上海" Or AA Like "深圳" Or AA Like "沈阳" Or AA Like "石家" Or AA Like "潍坊" Or AA Like "无锡" Or AA Like "武汉" Or AA Like "郑州" Or AA Like "总计" Then
AA.EntireRow.delete
End If
Next

Application.ScreenUpdating = True

End Sub
经测试，有明显改进。

Answer 2

Sub 去除2()
Dim AA As String
Dim i As Integer

i = 1
Do While Range("C" & i).Text <> "" '设置结束的条件,这里当单元格为空时结束,你可以设为别的条件
AA = Range("C" & i).Text
If AA Like "北京" Or AA Like "成都" Or AA Like "赤峰" Or AA Like "杭州" Or AA Like "上海" Or AA Like "深圳" Or AA Like "沈阳" Or AA Like "石家" Or AA Like "潍坊" Or AA Like "无锡" Or AA Like "武汉" Or AA Like "郑州" Or AA Like "总计" Then
Range("C" & i) = ""
End If
i = i + 1
Loop

End Sub

Answer 3

i=1
while i <= Range("A65536").End(xlUp).Row
AA = Range("A"&i).Value

if ...

Rows(i).Delete

else

i=i+1

endif

wend

Answer 4

整行删的话.有必要用VBA吗.用筛选就行了