Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range
In one line;
range("A1:A" & cstr(Range("A65536").End(xlUp).Row)).select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Sum() with variable range | Excel Worksheet Functions | |||
select range and put range address in variable | Excel Programming | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |