Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
Id 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 dont 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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" wrote: Id 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 dont 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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" wrote: 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" wrote: Id 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 dont 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
hi
how about this to select only 2 rows..... Range(ActiveCell, ActiveCell.Offset(1, 0).End(xlToRight)).Select regards FSt1 "Mats Samson" wrote: 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" wrote: 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" wrote: Id 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 dont 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
Hi Mats
I think this is what you are looking for: Range(Cells(ActiveCell.Row, 1), ActiveCell.End(xlToRight).Offset(1, 0)).Copy Regards, Per "Mats Samson" skrev i meddelelsen ... 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" wrote: 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" wrote: Id 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 dont 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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 "FSt1" wrote: hi how about this to select only 2 rows..... Range(ActiveCell, ActiveCell.Offset(1, 0).End(xlToRight)).Select regards FSt1 "Mats Samson" wrote: 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" wrote: 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" wrote: Id 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 dont 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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" wrote in message ... 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" wrote: 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" wrote: Id 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 dont 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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)" wrote: 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" wrote in message ... 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" wrote: 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" wrote: Id 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 dont 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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" wrote in message ... 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)" wrote: 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" wrote in message ... 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" wrote: 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" wrote: Id 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 dont 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
Some of the web based interfaces seem to add those extra dots.
"Rick Rothstein (MVP - VB)" wrote: I'm glad you got it working (although I don't see the extra dot that you mentioned in what I posted). Rick "Mats Samson" wrote in message ... 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)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... 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" wrote: 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" wrote: Id 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 dont 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? -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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" wrote in message ... Some of the web based interfaces seem to add those extra dots. "Rick Rothstein (MVP - VB)" wrote: I'm glad you got it working (although I don't see the extra dot that you mentioned in what I posted). Rick "Mats Samson" wrote in message ... 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)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... 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" wrote: 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" wrote: I?Td 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?Tt 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? -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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)" wrote: 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" wrote in message ... Some of the web based interfaces seem to add those extra dots. "Rick Rothstein (MVP - VB)" wrote: I'm glad you got it working (although I don't see the extra dot that you mentioned in what I posted). Rick "Mats Samson" wrote in message ... 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)" wrote: 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" wrote in message ... 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)" wrote: 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" wrote in message ... 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" wrote: 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" wrote: I?Td 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?Tt 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? -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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 "Rick Rothstein (MVP - VB)" wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extending a range selection
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/microsof...ic.excel.setup news://msnews.microsoft.com/microsoft.public.excel.misc news://msnews.microsoft.com/microsof...heet.functions news://msnews.microsoft.com/microsof...excel.newusers news://msnews.microsoft.com/microsof...el.programming (and a few more for MSWord) news://msnews.microsoft.com/microsof....docmanagement news://msnews.microsoft.com/microsof...word.word97vba news://msnews.microsoft.com/microsof....word.newusers news://msnews.microsoft.com/microsof...ord.pagelayout news://msnews.microsoft.com/microsof...ord.vba.addins news://msnews.microsoft.com/microsof....vba.beginners news://msnews.microsoft.com/microsof....customization news://msnews.microsoft.com/microsof...rd.vba.general news://msnews.microsoft.com/microsof....vba.userforms news://msnews.microsoft.com/microsof....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_tut...e_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_gr...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. Mats Samson wrote: 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 "Rick Rothstein (MVP - VB)" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extending a range | Excel Discussion (Misc queries) | |||
Extending a range | Excel Programming | |||
Extending Sort Range | Excel Worksheet Functions | |||
extending selection | Excel Discussion (Misc queries) | |||
Extending Selection | Excel Programming |