Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range without referring to specific cells
I've "discovered" the .Offset term in VBA and I see how it's used. I ca
select a cell to start at, then I can offset the selection by colum and row to get to a desired cell. So, now i'm wondering can I use thi offset function in combination with anything else to start in column and make a selection that ranges from column A to L? I can't specify range by cells becuase the data that I paste in always varys in length -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range without referring to specific cells
Range("A1").Resize(1,12).Select
selects A1:L1 however, you say it varies, so you could use something like Range(Range("A1"),Range("A1").End(xltoRight)).Sele ct Offset displaces from the specified location - if you want to expand, you can use Resize Range("A1").Resize(1,Range("A1").End(xltoright).Co lumn).Select as another example. You can also come from the right Range("A1").Resize(1,Range("IV1").End(xltoLeft).Co lumn).Select or Range(Range("A1"),Range("IV1").End(xltoLeft)).Sele ct -- Regards, Tom Ogilvy abxy wrote in message ... I've "discovered" the .Offset term in VBA and I see how it's used. I can select a cell to start at, then I can offset the selection by column and row to get to a desired cell. So, now i'm wondering can I use this offset function in combination with anything else to start in column A and make a selection that ranges from column A to L? I can't specify a range by cells becuase the data that I paste in always varys in length. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range without referring to specific cells
Ok, i don't know exactly what it is that i'm doing wrong (I suppose it's
becuase I don't understand .Resize fully right now, so maybe it'd help if i stop generalizing, and explain my exact problem. Ok, I paste data in excel(cell A8), but the length of the data varies. So, let's say for example, that I just pasted in my data and it's 10 rows long(si rught now, cells A8:A18 would be selectied). what I need is the equivalant of holding shift and press the right arrow until the selection reaches the L column...but without referring to particular cells, becuase the data length could be A8:18 this time, and A8:A76 the next. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range without referring to specific cells
Ok, i don't know exactly what it is that i'm doing wrong (I suppose
it's becuase I don't understand .Resize fully right now, so maybe it'd help if i stop generalizing, and explain my exact problem. Ok, I paste data in excel(cell A8), but the length of the data varies. So, let's say for example, that I just pasted in my data and it's 10 rows long(si rught now, cells A8:A18 would be selectied). what I need is the equivalant of holding shift and press the right arrow until the selection reaches the L column...but without referring to particular cells, becuase the data length could be A8:18 this time, and A8:A76 the next. --- Message posted from http://www.ExcelForum.com/ Hi try .... Dim rng as range set rng = Range("A8").Resize(rows_count,1) .... rng will be a range starting in A8, with rows_count rows and 1 column. Frank |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range without referring to specific cells
Range("A8").CurrentRegion.Select
should work regardless or if you only want to L and that won't change Range("A8").currentRegion.Resize(,12).Select will select the populated rows out to column L or if you have cells in column A selected Selection.End(xltoright).Select or to ensure to L and only out to L Select.resize(,12).Select -- Regards, Tom Ogilvy abxy wrote in message ... Ok, i don't know exactly what it is that i'm doing wrong (I suppose it's becuase I don't understand .Resize fully right now, so maybe it'd help if i stop generalizing, and explain my exact problem. Ok, I paste data in excel(cell A8), but the length of the data varies. So, let's say for example, that I just pasted in my data and it's 10 rows long(si rught now, cells A8:A18 would be selectied). what I need is the equivalant of holding shift and press the right arrow until the selection reaches the L column...but without referring to particular cells, becuase the data length could be A8:18 this time, and A8:A76 the next. --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range without referring to specific cells
oh my freaking goodness, I did it, I finally did it! This is a proble
i've been trying to tackle for at least 2 good months...this was th solution: Selection.Resize(,12).Select I had entered this statement before after the first reply from Tom, an it wasn't working, but I didn't enter it exactly as you see above, put in: Selection.Resize(*0* ,12).Select I was getting back an error when i ran the macro basically saying tha the range I was entering couldn't exist. It seems that leaving the out of the .Resize term was the trick :) And now, it works! Thanks a lot for the help guys!! I guess I just needed to fool aroun with it. lol, you just don't know how happy i am right now -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to specific cell in named range in Excel formula | Excel Worksheet Functions | |||
Selecting data in a specific date range using COUNTIFS function | Excel Worksheet Functions | |||
Selecting specific cells | Excel Discussion (Misc queries) | |||
Selecting values from a range that equal a specific total | Excel Worksheet Functions | |||
Selecting specific row/column from a named range | Excel Worksheet Functions |