Excel VBA，如何根据列中的数据选择行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9790924/
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
Excel VBA, How to select rows based on data in a column?
提问by Matt Ridge
Sub SelectAllReleventText()
Do While Range(“A1”).Offset(1, 6) <> Empty
Rows(ActiveCell.Row).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Here is my script, I've been told it doesn't do what it is meant to, which I expected since this was my first attempt. I am coming up with a variable not defined error. I thought I defined the variable, but I guess it wasn't specific enough for Excel VBA.
这是我的脚本，有人告诉我它没有达到预期的效果，因为这是我的第一次尝试。我想出了一个变量未定义错误。我以为我定义了变量，但我想它对于 Excel VBA 来说不够具体。
This is what I am attempting to do.
这就是我正在尝试做的。
- In Workbook 1, On B6 there is an alphanumeric name, I want that row to be selected.
- Go down one row, if there is text there select that row.
- Continue till text is no longer prevalent.
- Copy selected rows.
- Paste into another workbook (Workbook2), into tab 1, starting on row 2, since row 1 has headers.
- 在工作簿 1 中，在 B6 上有一个字母数字名称，我希望选择该行。
- 向下一行，如果那里有文本，请选择该行。
- 继续直到文本不再流行。
- 复制选定的行。
- 粘贴到另一个工作簿 (Workbook2) 的选项卡 1 中，从第 2 行开始，因为第 1 行有标题。
Thanks in advance. Just a heads up, I am using the Options Explicit in my VBA because I was told it was the "right way to do thing"...
提前致谢。提醒一下，我在 VBA 中使用了显式选项，因为有人告诉我这是“正确的做事方式”......
回答by Siddharth Rout
Yes using Option Explicitis a good habit. Using .Selecthowever is not :) it reduces the speed of the code. Also fully justify sheet names else the code will always run for the Activesheetwhich might not be what you actually wanted.
是的，使用Option Explicit是一个好习惯。.Select然而使用不是 :) 它降低了代码的速度。还要充分证明工作表名称的合理性，否则代码将始终运行，Activesheet这可能不是您真正想要的。
Is this what you are trying?
这是你正在尝试的吗？
Option Explicit
Sub Sample()
Dim lastRow As Long, i As Long
Dim CopyRange As Range
'~~> Change Sheet1 to relevant sheet name
With Sheets("Sheet1")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If Len(Trim(.Range("A" & i).Value)) <> 0 Then
If CopyRange Is Nothing Then
Set CopyRange = .Rows(i)
Else
Set CopyRange = Union(CopyRange, .Rows(i))
End If
Else
Exit For
End If
Next
If Not CopyRange Is Nothing Then
'~~> Change Sheet2 to relevant sheet name
CopyRange.Copy Sheets("Sheet2").Rows(1)
End If
End With
End Sub
NOTE
笔记
If if you have data from Row 2 till Row 10 and row 11 is blank and then you have data again from Row 12 then the above code will only copy data from Row 2 till Row 10
如果你有第 2 行到第 10 行的数据，第 11 行是空白的，然后你又有第 12 行的数据，那么上面的代码只会复制第 2 行到第 10 行的数据
If you want to copy all rows which have data then use this code.
如果要复制所有包含数据的行，请使用此代码。
Option Explicit
Sub Sample()
Dim lastRow As Long, i As Long
Dim CopyRange As Range
'~~> Change Sheet1 to relevant sheet name
With Sheets("Sheet1")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If Len(Trim(.Range("A" & i).Value)) <> 0 Then
If CopyRange Is Nothing Then
Set CopyRange = .Rows(i)
Else
Set CopyRange = Union(CopyRange, .Rows(i))
End If
End If
Next
If Not CopyRange Is Nothing Then
'~~> Change Sheet2 to relevant sheet name
CopyRange.Copy Sheets("Sheet2").Rows(1)
End If
End With
End Sub
Hope this is what you wanted?
希望这是你想要的吗？
Sid
锡德
回答by Tmdean
The easiest way to do it is to use the Endmethod, which is gives you the cell that you reach by pressing the end key and then a direction when you're on a cell (in this case B6). This won't give you what you expect if B6 or B7 is empty, though.
最简单的方法是使用该End方法，该方法为您提供按结束键到达的单元格，然后在您在单元格上时提供方向（在本例中为 B6）。但是，如果 B6 或 B7 是空的，这不会给你你所期望的。
Dim start_cell As Range
Set start_cell = Range("[Workbook1.xlsx]Sheet1!B6")
Range(start_cell, start_cell.End(xlDown)).Copy Range("[Workbook2.xlsx]Sheet1!A2")
If you can't use End, then you would have to use a loop.
如果不能使用End，则必须使用循环。
Dim start_cell As Range, end_cell As Range
Set start_cell = Range("[Workbook1.xlsx]Sheet1!B6")
Set end_cell = start_cell
Do Until IsEmpty(end_cell.Offset(1, 0))
Set end_cell = end_cell.Offset(1, 0)
Loop
Range(start_cell, end_cell).Copy Range("[Workbook2.xlsx]Sheet1!A2")
