Using xltoRight twice

1
votes

I have a certain excel table with merged cells, so I have to use ctrl+shift+right twice to select it completely, recording a macro yields the code for that to be a xlToright applied twice to the selection, but when I try to do the same thing with the code it doesn't work, any reason for that?

Thanks.

Edit : (Code) When I record a macro for selecting the table it gives(the table has 3 columns , each one having two merged cells)

Range("V29:W29").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlToRight)).Select

However running the same code does not select all 3 columns, only the first xlToRight works.

2
vba
Sorry but I don't understand your "question". Can you add some code to your question? – user2140173
Can't you use range("a1").CurrentRegion ? Eventually combined with Intersect(), that solves a lot of issues. – Patrick Honorez
@iDevlop I have used the CurrentRegion to do it and yes it works, basically I just want to ask as to why using xlToRight twice doesn't work. – Saurabh
Without the ability to see/examine the data, the xlToRight twice trick is hard to guess ! – Patrick Honorez

2 Answers

4
votes

Heey OP,

XltoRight cant be used on different lines since the code will just do the same selection as the previous.

If u want to do the proper selection with the XLtoRight u can put it in the same line of code like:

Range(Selection, Selection.End(xlDown).End(xlToRight).End(xlToRight)).Select

This will select down and twice right :)

hope this will answer your question and good luck with the code.

Cheers ~Blaze~

0
votes

You can use Resize to expand the Selection slowly to the right, instead.

If you want to expand it by 3 columns, use:

Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 3).Select

If set in a loop or something, you can progressively expand as you need, while controlling it with variables.