Select a Range Based on Active Cell Location?

[w01f]

Hi All,

I'd like to know if the following is possible within Excel VBA and if so how to implement it please?

I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.

For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).

The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).

Hopefully I've explained this ok, but if not I'm happy to help with any more info you need.

Thanks!

[StephenR]

Like this?

Please Login or Register  to view this content.

Note that activating and selecting are rarely necessary and should be avoided where possible.

[royUK]

You can add variable to replace the row 7 column referncesThe main point i that you do not generally need to select ranges. Try

Please Login or Register  to view this content.

[w01f]

Brilliant!!

As is always the case, so obvious on hindsight!

Thanks.

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save