Selecting a range without referring to specific cells

  • Thread starter Thread starter abxy
  • Start date Start date
A

abxy

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
 
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)).Select

Offset displaces from the specified location - if you want to expand, you
can use Resize

Range("A1").Resize(1,Range("A1").End(xltoright).Column).Select

as another example.

You can also come from the right
Range("A1").Resize(1,Range("IV1").End(xltoLeft).Column).Select

or
Range(Range("A1"),Range("IV1").End(xltoLeft)).Select
 
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.
 
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.
Click to expand...

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
 
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
 
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
 
Back
Top