vba 创建 Excel 宏以一次删除多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13032645/
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
Creating an Excel Macro to Delete multiple rows at once
提问by Jeff
I found a code online and want to make edits to it. The code is in VBA and I want the macro code to delete multiple rows rather than one. Here is the code:
我在网上找到了一个代码，想对其进行编辑。代码在 VBA 中，我希望宏代码删除多行而不是一行。这是代码：
Sub findDelete()
Dim c As String
Dim Rng As Range
c = InputBox("FIND WHAT?")
Set Rng = Nothing
Set Rng = Range("A:A").Find(what:=c, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Rng.EntireRow.Delete shift:=xlUp
End Sub
回答by Scott Holtzman
Instead of using find, use Autofilterand delete the VisibleCells
而不是使用查找，使用Autofilter和删除VisibleCells
Sub findDelete()
Dim c As String, Rng As Range, wks as Worksheet
c = InputBox("FIND WHAT?")
Set wks = Sheets(1) '-> change to suit your needs
Set Rng = wks.Range("A:A").Find(c, After:=Range("A1"), LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
With wks
.Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).AutoFilter 1, c
Set Rng = Intersect(.UsedRange, .UsedRange.Offset(1), .Range("A:A")).SpecialCells(xlCellTypeVisible)
Rng.Offset(1).EntireRow.Delete
End With
End If
End Sub
EDIT
编辑
To replace the InputBox with Multiple Values to Find / Delete Do This:
用多个值替换输入框以查找/删除执行以下操作：
Option Explicit
Sub FindAndDeleteValues()
Dim strValues() as String
strValues() = Split("these,are,my,values",",")
Dim i as Integer
For i = LBound(strValues()) to UBound(strValues())
Dim c As String, Rng As Range, wks as Worksheet
c = strValues(i)
'.... then continue with code as above ...
Next
End Sub
回答by Jamie Bull
Just wrap it up in a Whileloop.
只需将其包裹在一个While循环中即可。
Sub findDelete()
Dim c As String
Dim Rng As Range
c = InputBox("FIND WHAT?")
Set Rng = Nothing
Do While Not Range("A:A").Find(what:=c) Is Nothing
Set Rng = Range("A:A").Find(what:=c, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Rng.EntireRow.Delete shift:=xlUp
Loop
End Sub
回答by Olle Sj?gren
You already have the code to delete rows in Rng.EntireRow.Delete shift:=xlUp, what you need is the code to set the range to the rows which you want to delete. As usual in VBA, this can be done in a lot of ways:
您已经有了删除行的代码Rng.EntireRow.Delete shift:=xlUp，您需要的是将范围设置为要删除的行的代码。像在 VBA 中一样，这可以通过多种方式完成：
'***** By using the Rng object
Set Rng = Rows("3:5")
Rng.EntireRow.Delete shift:=xlUp
Set Rng = Nothing
'***** Directly
Rows("3:5").EntireRow.Delete shift:=xlUp
Your Findstatement only finds the first occurrence of c, that's why it's not deleting more that one row.
您的Find语句只找到第一次出现的c，这就是为什么它不会删除更多的行。
