Offset and Range Selection
 

I want to go to the last populated cell and then select the next 10 X 6
cells down. I am not sure how do this using offset but it does need to be
variable since my list varies in length.

Ex.

Last cell with a value is A117 ( I can use the end down to get there) I want
to select range (A118:F128)

I have tried several guesses and none have worked so far.


Thanks

Offset and Range Selection
 

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Cells(LastRow, 1).Resize(10, 6)

Rng now becomes your 10x6 range, though you should try and avoid selecting if at
all possible, but if you really need to then:-

Rng.Select will now do what you want.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Michael" wrote in message
...
I want to go to the last populated cell and then select the next 10 X 6
cells down. I am not sure how do this using offset but it does need to be
variable since my list varies in length.

Ex.

Last cell with a value is A117 ( I can use the end down to get there) I want
to select range (A118:F128)

I have tried several guesses and none have worked so far.


Thanks




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004

Offset and Range Selection
 

One way:

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(10, 6).Select

Note that this would select A118:F127, not A118:F128.


In article ,
"Michael" wrote:

I want to go to the last populated cell and then select the next 10 X 6
cells down. I am not sure how do this using offset but it does need to be
variable since my list varies in length.

Ex.

Last cell with a value is A117 ( I can use the end down to get there) I want
to select range (A118:F128)

I have tried several guesses and none have worked so far.


Thanks