What is wrong with the following VBA Code related to range selection?

Question

Set dr = Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp)(xlToRight))

Basically, I want to select a whole range spanning across numerous rows and columns with data being populated interspersed

It works perfectly upto Xlup. But when I add Xltoright, it doesn't take effect.

enter image description here

Share a screen-shot of your data, and highlight the range you are trying to `Select`. B.T.W, there's no need to `Select` the range, if you want later on to `Copy` it, you can directly `Copy` the range — Shai Rado, Mar 28 '18 at 06:43
Hi @ShaiRado , Thanks for replying. I don't want to copy the range. Rather I want to do something else — Gowtham, Mar 28 '18 at 06:48
try doing it with `Set dr = Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp).End(xlToRight))` — Romcel Geluz, Mar 28 '18 at 06:54
what do you think the `xlToRight` is doing on its own there? — Kami Kaze, Mar 28 '18 at 06:54
`End(xlUp)(xlToRight)` ? This is not valid. Use `End(xlUp).End(xlToRight)` — ZygD, Mar 28 '18 at 07:19

QHarr · Answer 1 · 2018-03-28T07:29:24.940

If you are trying to go right and down of current selection then consider the following code.

As you later attempt to select in sheet 1 I am going to assume you actually meant Worksheet("Sheet1").

If that is the case you can more safely do the following:

Sub hi()

    Dim dr As Range

    With ThisWorkbook.Worksheets("Sheet1")

        Set dr = .Range(Selection, .Cells(.Rows.Count, Selection.Column).End(xlUp).End(xlToRight))

    End With

    Debug.Print dr.Address

End Sub

Note:

Bear in mind that Cells /Range without a qualifying Worksheet will use the Activesheet. See the excellent discussion here:

Is the . in .Range necessary when defined by .Cells?.

Synopsis:

With "." is faster, avoids potential for error 1004 and is generally considered best practice.

Did the above make sense or is there something further I need to add? — QHarr, Apr 03 '18 at 17:57

What is wrong with the following VBA Code related to range selection?

1 Answers1