Hey all. I am sure you all have seen the basic code listed below. What I need
to know is how to change the "Selection.end(xldown)" part to a variable
place, like 100 columns to the right?


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

Do you mean search down 100 columns to the right, or search down then 100
columns to the right? If the later

Range(Selection, Selection.End(xlDown)).Offset(0,100).Select


--

HTH

RP
(remove nothere from the email address if mailing direct)


"havocdragon" wrote in message
...
Hey all. I am sure you all have seen the basic code listed below. What I
need
to know is how to change the "Selection.end(xldown)" part to a variable
place, like 100 columns to the right?


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

I mean instead of selection.end(xldown) is there another method of selecting
without a variable. For instance say I want to select 100 cells down from the
current cell in A1, however the current cell will not always be A1, it can be
variable between columns. How would I change the selection so it selected 100
cells down from the current, or even better, how could I use a created
variable that says how many cells to go down apply?

"Bob Phillips" wrote:

Do you mean search down 100 columns to the right, or search down then 100
columns to the right? If the later

Range(Selection, Selection.End(xlDown)).Offset(0,100).Select


--

HTH

RP
(remove nothere from the email address if mailing direct)


"havocdragon" wrote in message
...
Hey all. I am sure you all have seen the basic code listed below. What I
need
to know is how to change the "Selection.end(xldown)" part to a variable
place, like 100 columns to the right?


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

Take a look at .resize:

dim myCell as range
dim NumOfRows as long
dim numofcols as long
dim myRng as range

set mycell = activesheet.range("x99")
numofrows = 100
set myrng = mycell.resize(numofrows)
'now myrng is 100 rows

numofcols = 18
set myrng = mycell.resize(numofrows,numofcols)
'now myrng is 100 rows by 18 columns.

If you don't resize one of the dimensions, it doesn't change:

set myrng = range("a1:C3")
set myrng = myrng.resize(100) '100 rows by 3 columns

set myrng = range("a1:C3")
set myrng = myrng.resize(,31) '3 rows by 31 columns.

When you're looking at .resize, you may want to look at .offset. You can "move"
right/left/up/down with that.


havocdragon wrote:

I mean instead of selection.end(xldown) is there another method of selecting
without a variable. For instance say I want to select 100 cells down from the
current cell in A1, however the current cell will not always be A1, it can be
variable between columns. How would I change the selection so it selected 100
cells down from the current, or even better, how could I use a created
variable that says how many cells to go down apply?

"Bob Phillips" wrote:

Do you mean search down 100 columns to the right, or search down then 100
columns to the right? If the later

Range(Selection, Selection.End(xlDown)).Offset(0,100).Select


--

HTH

RP
(remove nothere from the email address if mailing direct)


"havocdragon" wrote in message
...
Hey all. I am sure you all have seen the basic code listed below. What I
need
to know is how to change the "Selection.end(xldown)" part to a variable
place, like 100 columns to the right?


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





--

Dave Peterson