Dynamic range over several columns

[jaffacake]

Hi all,

I have a workbook containing summary data that needs to be circulated via email on almost a daily basis.

Currently, the cells I need to circulate are in the range A1:K28. The range can and will change as more rows are added/deleted.

I'm guessing I need a dynamic range but from the reading I've done the examples only refer to a single column and not a selection as i've described.

Can anyone suggest a method for selecting this dynamic range?

Thanks in advance,

 

[Andrew Poulsom]

Like this?

=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

 

[jaffacake]

Hi Andrew,

Thanks for the response.

I should have mentioned that the reason that i'm limiting the range to K28 because there is additional information below this range that i do not want selecting.

New rows maybe entered above K28 naturally expanding the selected area.

I hope this helps?

Cheers,

 

[Andrew Poulsom]

If the range is surrounded by blank cells and you are using a macro:

Range("A1").CurrentRegion

will return it.

 

[jaffacake]

Works a treat - thanks Andrew