CODEWITHSUNDEEP
excelvba
kera_404
kera_404

Reputation: 123

VBA : Range method not giving desired output

I am trying to select a particular column's values using this code

Set rng = Range("O2", Range("O2").End(xlDown))

But somehow this is not working, I even tried using

Set rng = ThisWorkbook.Sheets("Sample").Range("O2", Range("O2").End(xlDown))

which doesn't either.

Upvotes: 1

Views: 119

Answers (2)

brettdj
brettdj

Reputation: 55692

This worked, but I am confused why Set rng = Range("O2", Range("O2").End(xlDown)) didn't work

Were the cells below O2 all blank, or were there hidden rows?

Using Find is more robust than the xlUp and xlDown techniques. It does deal with the issues I raise above (hidden rows etc)

Sub AnotherWay()
Dim rng1 As Range
Set rng1 = Range("O:O").Find("*", [o1], xlFormulas, , , xlPrevious)
If rng1.Row > 2 Then
    Set rng2 = Range(rng1, [o2])
    Debug.Print rng2.Address
Else
    MsgBox "range invalid"
End If
End Sub

Upvotes: 1

Stupid_Intern
Stupid_Intern

Reputation: 3450

Your code works fine and will give you desired results if there are no blank cells in the column otherwise it will give you the wrong address

You can do the same thing like this

Dim frow As Long
frow = Worksheets("Sample").Range("O" & Rows.Count).End(xlUp).Row
Set rng = Worksheets("Sample").Range("O2:O" & frow)
Debug.Print rng.Address

Upvotes: 0

Related Questions