excel vba: Range gives Argument Not Optional error message

Question

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:

 Range("O2", Range.End(xlDown))

But that fails with Argument Not Optional. What am i doing wrong? I'm using Excel 2010.

score 5 · Answer 1 · answered Dec 20 '13 at 15:18

Don't use xlDown Declare your Objects and then work with it.

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

score 3 · Answer 2 · edited May 23 '17 at 10:32

3

To select the range from O2 to the last filled cell in that column, you could use:

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 Select unless 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:

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

edited May 23 '17 at 10:32

Community

1
1

answered Dec 20 '13 at 15:20

Doug Glancy

27,214
6
67
115

+ 1 :) The other problem with `xldown` is that if `O2` is the only cell which has data then `xldown` will select the entire range up till the end of Excel rows. – Siddharth Rout Dec 20 '13 at 15:24
1

That's true. +1 to you too. Looks like I was almost as fast ;). – Doug Glancy Dec 20 '13 at 15:31
Your Triple hat is slowing you down :) – Siddharth Rout Dec 20 '13 at 15:32

score 2 · Answer 3 · answered Dec 20 '13 at 15:12

2

Maybe you need

Range("O2", Range("O2").End(xlDown)).Select

?

answered Dec 20 '13 at 15:12

ttaaoossuuuu

7,786
3
28
58

excel vba: Range gives Argument Not Optional error message

3 Answers3