vba 使用VBA在Excel中选择多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26276285/
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
Selecting Multiple rows in Excel using VBA
提问by Andrew
I am trying to process some rows in a sheet with VBA. I want to cut and paste a set of rows from one sheet to the other and struggling to work out the code I need to ID and select the last row to ID the whole range.
我正在尝试使用 VBA 处理工作表中的一些行。我想将一组行从一张纸剪切并粘贴到另一张纸上，并努力计算出我需要 ID 的代码并选择最后一行来 ID 整个范围。
The rows I want are Identified by the value in column A, lets say the value is 'Dept1' in that cell, then I need to copy that row and any other row with that value in the cell.
我想要的行由 A 列中的值标识，假设该单元格中的值为“Dept1”，然后我需要复制该行以及该单元格中具有该值的任何其他行。
The values will then be pasted into another sheet, which I have created along with the headers.
然后将这些值粘贴到我与标题一起创建的另一个工作表中。
My first try at this involved sorting the data by the column A and then looping through the data until I found the first cell with 'Dept1' and then put this cell address into a variable, but I also need to ID the last 'dept1' value so I can get the row number for this.
我的第一次尝试涉及按 A 列对数据进行排序，然后循环遍历数据，直到找到带有“Dept1”的第一个单元格，然后将此单元格地址放入一个变量中，但我还需要标识最后一个“dept1”值，以便我可以为此获取行号。
The code I have so far is this, which only puts the first cell address in to variable but also need the last cell address to then create the range I want to select and cut:
到目前为止，我的代码是这样的，它只将第一个单元格地址放入变量中，但还需要最后一个单元格地址来创建我想要选择和剪切的范围：
With wb.ActiveSheet
'Call sortorder sub to sort Department field
SortOrder
For i = 1 To lastcol
'find department named column
If .Range(ConvertToLetter(i) & 1).Value = "department" Then
For j = 2 To MaxRowCount
If .Range(ConvertToLetter(i) & j).Value = "Dept1" Then
'Get first cell address here to build range for 'dept1' data
firstRangeNumber = ConvertToLetter(i) & j
RHSCRange = firstRangeNumber & ":"
' Create code to populate LastRangeNumber variable as explained below
' work out how to get last cell address number with 'dept1'
' and use lastcol variable value combined with last cell row number(j)
' to create last cell address for range. Finally combine first and
' last variables to create complete range, select and cut range to
' New sheet
End If
Next
End If
Next
End With
回答by TylerH
Solved this by using the find function, therefore no need to loop through rows and columns:
通过使用 find 函数解决了这个问题，因此不需要遍历行和列：
LastRow = Cells.Find("Dept1", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
firstrow = Cells.Find("Dept1", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
If you are going to use this make sure you sort your data first by the column you are searching - in this case the Department I am looking for is 'Dept1', so you can select the first and last row with 'Find', then select the whole range using the first and last row number (note: not the absolute first and last row number of data but of the range you are looking for):
如果您要使用它，请确保首先按您要搜索的列对数据进行排序 - 在这种情况下，我要查找的部门是“Dept1”，因此您可以使用“查找”选择第一行和最后一行，然后使用第一个和最后一个行号选择整个范围（注意：不是数据的绝对第一个和最后一个行号，而是您要查找的范围）：
Range = ConvertToLetter(1) & firstRow & ":" & ConvertToLetter(lastcol) & LastRow
Note: the Lastcol variable comes from a global variable set like this:
注意：Lastcol 变量来自一个全局变量集，如下所示：
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
And the ConvertToLetter function is a piece of code you can find online to convert a number to a column character 1=A. I don't strictly need this now (replaced i with 1) as originally I was using a loop through the columns (For i = 1 To lastcol....next) to find the relevant column, and also was doing a loop with rows to look for the 'Dept1' value.
ConvertToLetter 函数是一段代码，您可以在网上找到将数字转换为列字符 1=A。我现在并不严格需要这个（用 1 替换 i），因为最初我使用一个循环遍历列（For i = 1 To lastcol ....next）来找到相关的列，并且也在做一个循环行以查找“Dept1”值。
Then select, cut/copy and then paste range:
然后选择，剪切/复制，然后粘贴范围：
Range(Range).Select
Selection.Cut
'And then paste where you want it to go.
