Variable Range
 

How can the following range statment: "ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave

Variable Range
 

In one line;

range("A1:A" & cstr(Range("A65536").End(xlUp).Row)).select

Variable Range
 

Hi Dave,

It is not clear where you are headed or what you are trying to get away from.
But my guess is that you are trying to make it more generic so that it can be
used anywhere, and the use of INTERSECT would help you reduce a
section. Of course in a macro you really want to avoid changing the
actual selection on the worksheet unless that is the purpose of the macro.

See if the follow page helps helps.
.. http://www.mvps.org/dmcritchie/excel/proper.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave" wrote in message ...
How can the following range statment: "ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave

Variable Range
 

Dave,

Not sure what you're trying to do here - the statement
"Activecell.range("A1:A300").select doesn't select A1:A300 - it is
relevative to where you are in the spreadsheet i.e. if the current
active cell was D20 then your selection would be from D20:D320.

If you have a dynamic range in column A then this would sort out the
range for you:

Range("A1:A" & Range("A65536").End(xlUp).Row).Select

Apologies if this is not what you are looking for - I may have
misunderstood.

Variable Range
 

Selection.rows.count means that area is already selected. If you want to
expand the selection

selection.Resize(selection.rows.count,10).select
to expand to 10 columns same number of rows, or the simple

Selection.Resize(,10).Select

--
Regards,
Tom Ogilvy


"Dave" wrote:

How can the following range statment: "ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave

Variable Range
 

Sorry for not being clear. I want the row, column range in:
"ActiveCell.Range("A1:A300").Select" to be based on "Selection.Rows.Count"



"Tom Ogilvy" wrote:

Selection.rows.count means that area is already selected. If you want to
expand the selection

selection.Resize(selection.rows.count,10).select
to expand to 10 columns same number of rows, or the simple

Selection.Resize(,10).Select

--
Regards,
Tom Ogilvy


"Dave" wrote:

How can the following range statment: "ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave

Variable Range
 

Try this

Say, as an example, I have a single cell selected and it is e9. Now what
command would I use so that such and such a range is selected after I run
the command

or

how would I get the actual row in
ActiveCell.Range("A1:A300")

a: ActiveCell.Row + 300 - 1

or whatever you question actually is.

--
Regards,
Tom Ogilvy


"Dave" wrote:

Sorry for not being clear. I want the row, column range in:
"ActiveCell.Range("A1:A300").Select" to be based on "Selection.Rows.Count"



"Tom Ogilvy" wrote:

Selection.rows.count means that area is already selected. If you want to
expand the selection

selection.Resize(selection.rows.count,10).select
to expand to 10 columns same number of rows, or the simple

Selection.Resize(,10).Select

--
Regards,
Tom Ogilvy


"Dave" wrote:

How can the following range statment: "ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave

Variable Range
 

This is what I was trying to do:
NRows = Selection.Rows.Count
ActiveCell.Range("A1", "A" & NRows).Select

Thank You
dave


"Tom Ogilvy" wrote:

Try this

Say, as an example, I have a single cell selected and it is e9. Now what
command would I use so that such and such a range is selected after I run
the command

or

how would I get the actual row in
ActiveCell.Range("A1:A300")

a: ActiveCell.Row + 300 - 1

or whatever you question actually is.

--
Regards,
Tom Ogilvy


"Dave" wrote:

Sorry for not being clear. I want the row, column range in:
"ActiveCell.Range("A1:A300").Select" to be based on "Selection.Rows.Count"



"Tom Ogilvy" wrote:

Selection.rows.count means that area is already selected. If you want to
expand the selection

selection.Resize(selection.rows.count,10).select
to expand to 10 columns same number of rows, or the simple

Selection.Resize(,10).Select

--
Regards,
Tom Ogilvy


"Dave" wrote:

How can the following range statment: "ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave

Variable Range
 

Assuming ActiveCell is the upperleft corner of the selection

Selection.Columns(1).Select

would do the same.

if not
Intersect(ActiveCell.EntireColumn,Selection).Selec t

would do it.

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...
This is what I was trying to do:
NRows = Selection.Rows.Count
ActiveCell.Range("A1", "A" & NRows).Select

Thank You
dave


"Tom Ogilvy" wrote:

Try this

Say, as an example, I have a single cell selected and it is e9. Now
what
command would I use so that such and such a range is selected after I
run
the command

or

how would I get the actual row in
ActiveCell.Range("A1:A300")

a: ActiveCell.Row + 300 - 1

or whatever you question actually is.

--
Regards,
Tom Ogilvy


"Dave" wrote:

Sorry for not being clear. I want the row, column range in:
"ActiveCell.Range("A1:A300").Select" to be based on
"Selection.Rows.Count"



"Tom Ogilvy" wrote:

Selection.rows.count means that area is already selected. If you
want to
expand the selection

selection.Resize(selection.rows.count,10).select
to expand to 10 columns same number of rows, or the simple

Selection.Resize(,10).Select

--
Regards,
Tom Ogilvy


"Dave" wrote:

How can the following range statment:
"ActiveCell.Range("A1:A300").Select" be
written for a varialble range based on "Selection.Rows.Count" ?

Thank you
dave