using a the active cell in a range command....
 

i am using a loop and active cell offset to get to the first empty cell in a
specified column.

Do
ActiveCell.offset(1, 0).Select
Loop Until ActiveCell.Value = ""

after arriving at the active cell that the loop selects, i want to use the
active cell in a range command.

Range("A10:Ai2000").Select

i want to use whatever the active cell is in place of the "A10" above.

any ideas?




--
dr chuck

using a the active cell in a range command....
 

probably I have not understood;l
activecell.End(xlDown).offset(1,0).Select
will take to the first empty cell (it should be really empty) in the column

if your want to select the range from the activecell upto the first empty
cell then use

range(activecell,activecell.End(xlDown).Offset(1,0 )).Select

This will select from activecell to the first empty cell.


"dr chuck" wrote in message
...
i am using a loop and active cell offset to get to the first empty cell in
a
specified column.

Do
ActiveCell.offset(1, 0).Select
Loop Until ActiveCell.Value = ""

after arriving at the active cell that the loop selects, i want to use
the
active cell in a range command.

Range("A10:Ai2000").Select

i want to use whatever the active cell is in place of the "A10" above.

any ideas?




--
dr chuck

using a the active cell in a range command....
 

Hi Dr Chuck,

To find the first empty cell, try:

Dim rng As Range
Dim rng2 As Range

Set rng = Range("A1").End(xlDown)(2)

To find cell after the last populated cell in column A, try:

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)

To return your extended range, try:

Set rng2 = Range(rng, Range("A2000"))

Note, however, that it is rarely necessary, and usually inefficient, to make
selections. It is instead usually possible to manipulate the range object,
e.g.:

rng2.Interior.ColorIndex = 6


---
Regards,
Norman


"dr chuck" wrote in message
...
i am using a loop and active cell offset to get to the first empty cell in
a
specified column.

Do
ActiveCell.offset(1, 0).Select
Loop Until ActiveCell.Value = ""

after arriving at the active cell that the loop selects, i want to use
the
active cell in a range command.

Range("A10:Ai2000").Select

i want to use whatever the active cell is in place of the "A10" above.

any ideas?




--
dr chuck

using a the active cell in a range command....
 

thanks norman,
I will try this method
--
dr chuck


"Norman Jones" wrote:

Hi Dr Chuck,

To find the first empty cell, try:

Dim rng As Range
Dim rng2 As Range

Set rng = Range("A1").End(xlDown)(2)

To find cell after the last populated cell in column A, try:

Set rng = Cells(Rows.Count, "A").End(xlUp)(2)

To return your extended range, try:

Set rng2 = Range(rng, Range("A2000"))

Note, however, that it is rarely necessary, and usually inefficient, to make
selections. It is instead usually possible to manipulate the range object,
e.g.:

rng2.Interior.ColorIndex = 6


---
Regards,
Norman


"dr chuck" wrote in message
...
i am using a loop and active cell offset to get to the first empty cell in
a
specified column.

Do
ActiveCell.offset(1, 0).Select
Loop Until ActiveCell.Value = ""

after arriving at the active cell that the loop selects, i want to use
the
active cell in a range command.

Range("A10:Ai2000").Select

i want to use whatever the active cell is in place of the "A10" above.

any ideas?




--
dr chuck

using a the active cell in a range command....
 

Thanks for your help. I am just learning to use visual basic. So, the obvious
answers are sometimes hidden in my lack of knowledge.
--
dr chuck


"R..VENKATARAMAN" wrote:

probably I have not understood;l
activecell.End(xlDown).offset(1,0).Select
will take to the first empty cell (it should be really empty) in the column

if your want to select the range from the activecell upto the first empty
cell then use

range(activecell,activecell.End(xlDown).Offset(1,0 )).Select

This will select from activecell to the first empty cell.


"dr chuck" wrote in message
...
i am using a loop and active cell offset to get to the first empty cell in
a
specified column.

Do
ActiveCell.offset(1, 0).Select
Loop Until ActiveCell.Value = ""

after arriving at the active cell that the loop selects, i want to use
the
active cell in a range command.

Range("A10:Ai2000").Select

i want to use whatever the active cell is in place of the "A10" above.

any ideas?




--
dr chuck