vba Excel：选择所有行直到空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10353367/
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: Selecting all rows until empty cell
提问by Daan
I'd to select all rows from 2 adjacent columns, starting with the row below the header (this is fixed) and ending with the row before the first row with a blank cell.
我会从 2 个相邻列中选择所有行，从标题下方的行开始（这是固定的），并以带有空白单元格的第一行之前的行结束。
Given the following the example...
鉴于以下示例...
A B
1 Item Amount
2 Item1 1
3 Item2 4
4 2
5
I'd like to select the range A2:B3. The first row is always row 2, the last row is row 3 because it is the row before the first row with a blank cell.
我想选择范围 A2:B3。第一行总是第 2 行，最后一行是第 3 行，因为它是带有空白单元格的第一行之前的行。
My goal is to use this selection to automatically sort and add borders to the rows after I've completely filled in the row below the current last row. In this example, I'd have to fill in A4 to make the row part of the selection and apply the aforementioned actions.
我的目标是在我完全填充当前最后一行下方的行后，使用此选择自动对行进行排序和添加边框。在此示例中，我必须填写 A4 以制作选择的行部分并应用上述操作。
I hope anyone can help me.. Thanks in advance!
我希望任何人都可以帮助我.. 提前致谢！
EDIT
编辑
I've come up with a solution, based on chris neilsen's solution:
我想出了一个解决方案，基于克里斯尼尔森的解决方案：
Dim rng As Range
With Worksheets("YourSheet")
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 2).End(xlDown))
Set rng2 = .Range(.Cells(2, 2), .Cells(2, 1).End(xlDown))
Set r = Application.Intersect(rng1, rng2)
End With
回答by chris neilsen
try this
尝试这个
Dim rng as Range
With Worksheets("YourSheet")
Set rng = .Range(.Cells(1,2), .Cells(1,2).End(xlDown)).Resize(,2)
End With
the variable rngwill now be set to A2:B3
该变量rng现在将被设置为A2:B3
回答by chris neilsen
Try below code :
试试下面的代码：
Sub sample()
Dim lastRow As Long
lastRow = Range("A65000").End(xlUp).Row
Dim rng As Range, newRng As Range
Set rng = Range("A2:A" & lastRow)
Set newRng = rng.Resize(, 2)
If Not newRng Is Nothing Then
newRng.Sort key1:=Range("A2")
End If
newRng.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic
End Sub
