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.

Answer 1

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

Answer 2

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