Vba：根据文本选择多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13715245/
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
Vba: Select Multiple rows based on Text
提问by Ali
I have a list made up of 4 columns and undetermined rows. I am trying to collect only Col B and C where Col B has words Hymanpot and copy them in order to a new sheet. I have gotten the list to sort, but using UsedRange, copies all the rows. How do I only copy over B and C?
我有一个由 4 列和未确定行组成的列表。我试图只收集 Col B 和 C，其中 Col B 有单词 Hymanpot 并将它们复制到新表中。我已经得到了要排序的列表，但是使用 UsedRange 复制所有行。我如何只复制 B 和 C？
Range("A1").Activate
ActiveCell.EntireRow.Insert
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="Hymanpot"
ActiveSheet.UsedRange.Select
Col A | Col B | Col C | Col D
1 Stuff 1 1
1 MoreStuff 2 1
1 Hymanpot 3 1
1 Hymanpot 4 1
1 SomeStuff 5 1
1 Hymanpot 6 1
回答by Joseph
After you filter it, you can select the range B:C and then copy to your destination. I updated your code to show you an example. Also, you don't always need to "Select" anything with VBA (it's very rare to haveto do that).
过滤后，您可以选择范围 B:C，然后复制到您的目的地。我更新了您的代码以向您展示一个示例。此外，你并不总是需要用VBA“选择”任何东西（这是非常罕见的有这样做）。
Hope this helps
希望这可以帮助
Option Explicit
Sub doIt()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Range("A1:D1").AutoFilter Field:=2, Criteria1:="Hymanpot"
' make sure results were returned from the filter
If (sh.Cells(sh.Rows.Count, 1).End(xlUp).Address <> "$A") Then
Dim newSh As Worksheet
Set newSh = Sheets.Add
sh.Range("B:C").Copy newSh.Range("A1")
End If
End Sub
回答by Kevin Pope
First cut, although I do have some questions:
第一次剪辑，虽然我确实有一些问题：
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Results")
ws.Rows(1).AutoFilter Field:=2, Criteria1:="Hymanpot"
ws.Range("B2:C2", Range("B2:C2").End(xlDown)).Copy
ThisWorkbook.Worksheets("DestinationSheet").Range("A1").PasteSpecial
1) Will this be happening in the same sheet every time (ex. Worksheets("Results"))?
2) Do you want to include the header row? If so, change the .Copyline to B1:C1in both instances. The current implementation just takes the filtered content without the header.
1）这是否每次都发生在同一张纸上（例如Worksheets("Results")）？
2) 你想包括标题行吗？如果是这样，请在两种情况下将.Copy行更改为B1:C1。当前的实现只采用没有标题的过滤内容。
Obviously you'll need to change the worksheet names to match your implementation.
显然，您需要更改工作表名称以匹配您的实现。
回答by aevanko
Here's a simple way of doing it that does not rely on the auto-filter. Not sure speed-wise which is quicker, but I wanted to offer up another solution to your problem.
这是一种不依赖于自动过滤器的简单方法。不确定速度方面哪个更快，但我想为您的问题提供另一种解决方案。
Sub CopyHymanpot()
Application.ScreenUpdating = False
Dim lastRow As Long, i As Long, j As Long
j = 1
lastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To lastRow
If InStr(Range("B" & i).Value, "Hymanpot") Then
Rows(i).Copy Destination:=Sheets(2).Rows(j)
j = j + 1
End If
Next
Application.ScreenUpdating = True
MsgBox j - 1 & " row(s) copied to Sheet2."
End Sub
