vba 删除除包含特定文本的行以外的所有行的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19590181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to remove all rows except those containing certain text
提问by Xeo
I'm trying to write a Macro which will delete every row, apart from those which contain some specific text.
我正在尝试编写一个宏，它将删除除包含某些特定文本的行之外的每一行。
I need to have the following criteria:
我需要满足以下条件：
- Never delete the first 2 rows
- Exclude the rows where the word "Somme" can be found in column C or D.
- 永远不要删除前两行
- 排除在 C 或 D 列中可以找到“Somme”一词的行。
Note, the word Somme if part of a string in column C or D. An example of the text found would be something like:
请注意，单词 Somme 是 C 或 D 列中字符串的一部分。找到的文本示例如下：
Somme alpha/000284727819293
索姆阿尔法/000284727819293
What I have so far is code which deletes rows with Somme in it, however i need the opposite:
到目前为止，我所拥有的是删除包含 Somme 的行的代码，但是我需要相反的内容：
Sub CleanUp()
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("D3", ActiveSheet.Range("D65536").End(xlUp))
Do
Set c = SrchRng.Find("Somme", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
回答by Gary's Student
Give this a shot:
试一试：
Sub SaveSomeRows()
Dim N As Long, L As Long, r As Range
Dim s As String, v As String
Set r = ActiveSheet.Range("D3", ActiveSheet.Range("D65536").End(xlUp))
N = r.Count
s = "somme"
For L = N To 1 Step -1
v = LCase(r(L).Value)
If InStr(1, v, s) = 0 Then
r(L).EntireRow.Delete
End If
Next L
End Sub
EDIT#1:
编辑#1：
The initial version of the macro ignored column C.....try this instead:
宏的初始版本忽略了列C..... 试试这个：
Sub SaveSomeRows()
Dim N As Long, L As Long, r As Range
Dim s As String, v As String
Set r = ActiveSheet.Range("D3", ActiveSheet.Range("D65536").End(xlUp))
N = r.Count
s = "somme"
For L = N To 1 Step -1
v = LCase(r(L).Value & r(L).Offset(-1, 0).Value)
MsgBox v
If InStr(1, v, s) = 0 Then
r(L).EntireRow.Delete
End If
Next L
End Sub
