excel vba: Range 给出了 Argument Not Optional 错误消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20706585/
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: Range gives Argument Not Optional error message
提问by dr jerry
In excel vba I'm tryin to select a range of values starting in Cell"O2", (O from Oyster) down to the end of the sheet, I'm trying:
在 excel vba 中，我试图选择从 Cell"O2", (O from Oyster) 到工作表末尾的一系列值，我正在尝试：
Range("O2", Range.End(xlDown))
But that fails with Argument Not Optional. What am i doing wrong?
I'm using Excel 2010.
但这失败了Argument Not Optional。我究竟做错了什么？我正在使用 Excel 2010。
回答by Siddharth Rout
Don't use xlDownDeclare your Objects and then work with it.
不要使用xlDown声明您的对象然后使用它。
Use this
用这个
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim LRow As Long
Dim rng As Range
'~~> Change this to the relevant sheet name
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Find last row in Col O which has data
LRow = .Range("O" & .Rows.Count).End(xlUp).Row
'~~> This is your range
Set rng = .Range("O2:O" & LRow)
With rng
'~~> Whatever you want to do
End With
End With
End Sub
回答by Doug Glancy
To select the range from O2 to the last filled cell in that column, you could use:
要选择从 O2 到该列中最后一个填充单元格的范围，您可以使用：
Range("O2", Range("O2").End(xlDown)).Select
But that has a few problems, including the fact that it will "stop" at any blanks, and that you should avoid using Selectunless absolutely necessary. Also, you should get in the habit of qualifying your ranges, e.g., specifying which worksheet they're in. Given all that, I propose something like this, assuming you wanted to turn the cells in the range red:
但这有一些问题，包括它会在任何空白处“停止”，并且除非绝对必要，否则您应该避免使用 Select。此外，您应该养成限定范围的习惯，例如，指定它们在哪个工作表中。 鉴于所有这些，我提出了这样的建议，假设您想将范围内的单元格变成红色：
Sub test()
Dim LastRow As Long
Dim ws As Excel.Worksheet
Set ws = ActiveSheet
With ws
LastRow = .Range("O" & .Rows.Count).End(xlUp).Row
.Range("O2:O" & LastRow).Interior.Color = vbRed
End With
End Sub
回答by ttaaoossuu
Maybe you need
也许你需要
Range("O2", Range("O2").End(xlDown)).Select
?
?
