Select range dynamically using relative row and column references

[gtol]

Hi,

I am looking for some help with VBA syntax to select a range dynamically.

I have included an example in the attached workbook to help explain the problem. This is part of a bigger project.

Essentially, when a name is selected (using SelName), I want to select the range of cells on the selected row that correspond to the CurrentMonth and EndMonth columns.

In other parts of the project, I have been able to select fixed ranges no problem using a combination of Offset and Resize.

However, in this case, the selection needs to work dynamically (especially becuase the CurrentMonth won't be fixed in practice - it will roll forward each month).

I have been experimenting with .column and .row but without any success.

If someone can help me get the syntax right, I would be most grateful.


gtol

[sweep]

Hi,

Does this do what you need?

Please Login or Register  to view this content.

[gtol]

Hi Sweep,

Thanks for the quick reply !

Your code works a treat. I changed the address of CurrentMonth and it still picks up the correct range.

I can see that you are adding the value of the home column (2) to the value of the CurrentMonth to get the starting position of the dynamic range which makes sense.

Just an observation - I was half expecting a solution that referenced Range("CurrentMonth").column or something similar as the stating position. Is there a reason why you decided to work with the 'value' rather than the 'column' of the range ?

gtol

[sweep]

Just an observation - I was half expecting a solution that referenced Range("CurrentMonth").column or something similar as the stating position. Is there a reason why you decided to work with the 'value' rather than the 'column' of the range ?

Yup - mainly because I like to overcomplicate things. You're quite right of course, this makes much more sense!!

Please Login or Register  to view this content.

[gtol]

Hi Sweep,

That is EXACTLY what I was looking for and, now that i see the syntax, it makes perfect sense.
I can use this gem in lots of parts of my project.

Thanks a million !

gtol