Extending a range selection

[Mats Samson]

I’d like to extend the range selection of the CURRENT row from A column to 2
(adjacent) rows until End(xlRight) and then copy the selection
I don’t seem to get it right having selected 2 rows and trying to extend the
selection until End(xlRight). Can anybody help with the syntax, please?

 

[FSt1]

hi
if i understand you correctly, you are somewhere in A column and want to
select 2 rows down and to the end of the data. Assuming you have a solid
block of data, try this....
Range(ActiveCell, ActiveCell.Offset(2, 0).End(xlToRight)).Select

regards
FSt1

 

[Mats Samson]

Thanks FSt1,
well not exactly, I wanted to select 2 rows and copy them.
However, meanwhile I solved it:
Selection.End(xlToLeft).Range("A1:A2").Select
Range(Selection, Selection.End(xlToRight)).Copy

Thanks anyway
Mats

 

[FSt1]

hi
how about this to select only 2 rows.....
Range(ActiveCell, ActiveCell.Offset(1, 0).End(xlToRight)).Select

regards
FSt1

 

[Mats Samson]

I prefer my solution as I can start from any column, and the procedure
move leftwards to get the first column and then extends the range to 2 rows
and rightwards to the end of data.
Cheers
Mats

 

[Rick Rothstein \(MVP - VB\)]

I think this code also does what you want...

With Selection
.End(xlToLeft).Resize(2, .End(xlToRight).Column - ..End(xlToLeft).Column + 1).Copy
End With

Rick

 

[Mats Samson]

Thanks guys,
there seems to be lots of alternatives to this.
I like Pers option as it's accomplished in one row! Neat!
But Rick, what shall be in place of -. after .Column?
Best regards
Mats

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure I understand your question to me... there is something already
after the dash. Just in case you asked your question because what I posted
got cut-off in some way, here is the code again, but this time I used a line
continuation character to show the single-line statement on two lines (you
can combine it back into a single line after copy/pasting it into your
program)...

With Selection
.End(xlToLeft).Resize(2, .End(xlToRight).Column - _
.End(xlToLeft).Column + 1).Copy
End With

Rick

 

[Mats Samson]

Hi Rick,
yes, it works OK now! It was quite obvoius and probably it was too late but
I went only confused. You'd put an extra dot before End: (xlToRight).Column -
...End(xlToLeft)
Thanks for the interest!
Mats

 

[Rick Rothstein \(MVP - VB\)]

I'm glad you got it working (although I don't see the extra dot that you
mentioned in what I posted).

Rick

 

[Dave Peterson]

Some of the web based interfaces seem to add those extra dots.

 

[Rick Rothstein \(MVP - VB\)]

Thanks for pointing that out... I don't think I ever came across that
before. Is there any way to insure that doesn't happen? Format the code in
some way? Or just recommend to the person to switch to a "real" news reader
program?<g>

Rick

 

[Dave Peterson]

I used to see it in responses from people using google. But that seems to have
gone away.

But I looked at the headers from Mats' message and it looks like he used CDO to
post.

I don't think you can do anything except that recommendation to use a newsreader
that doesn't do it.

 

[Rick Rothstein \(MVP - VB\)]

I don't think you can do anything except that recommendation to use a

newsreader that doesn't do it.

Okay, thank you. As I said, this was a new one on me... I never had this
occur to me before, so it was somewhat of a surprise.

Rick

 

[Mats Samson]

Hello guys,
I don't understand this! What is CDO? I'm connecting to Office Online from
the link in the email message, i.e. through IE. So Ricks reply to me was read
directly in Office Online. Why would his first message suddenly put an extra
dot after Column-:
With Selection
..End(xlToLeft).Resize(2, .End(xlToRight).Column - ..End(xlToLeft).Column +
1).Copy
End With
but not the second, same "string".
When I write replies I use Word to compose my message and copy the message
to the Office Online New/Reply window. The reason is that Office Online logs
you out if you're idle to long when composing the message (yeah, yeah, I have
to stop and think once and a while) and then all what you've written is gone.
Cheers
Mats

 

[Dave Peterson]

CDO is the acronym that means that you're using MS web based interface.

And the bad news is that the web based stuff does sometimes add those extra
characters.

I have no idea why it's sometimes and not always.

You may want to switch to a real newsreader (Rick's suggestion).

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

=============
Sometimes, these newsreaders can't access the newsgroups from behind firewalls
(like at work). If you can't use a newsreader, you may want to switch to
Notepad (instead of MSWord).

There are lots of characters that get mangled when copy|pasting from MSWord into
a plain text message.