VBA Dynamic Selection

sub LastCellColM

Dim rngLastCell as Range
Dim MyRange as Range

Set rngLastCell = Range("M65536").End(xlUp)
Set MyRange = Range("A1", rngLastCell)

' do your thing

end sub



"AJMorgan591" wrote:

>
> Hi all,
>
> Long story short, is it possible in VBA to dynamically select cells in
> the range (for example) A1:Mx where x = the last row that contains data
> (i.e. non-blank cells). The blank cells (i.e. those cells after row x)
> do contain formulas linked to other worksheets but if the relevant
> cells in those other worksheets are blank then so too are these cells.
>
> Thanks in advance
>
>
> --
> AJMorgan591
> ------------------------------------------------------------------------
> AJMorgan591's Profile: http://www.excelforum.com/member.php...o&userid=27774
> View this thread: http://www.excelforum.com/showthread...hreadid=473647
>
>

A lot would depend on what is in the non-empty cells. If the formulas
return either a number or an empty string, then you could use special cells
to only go to the numbers, but if the formulas might return text as well,
then I think you would need to loop through them to determine where the last
cell is located that contains real data. Any method such as specialcells
or the end(xlup) as suggested by Cush, would see any cell with a formula as
containing data whether it appeared blank or not.

--
Regards,
Tom Ogilvy



"AJMorgan591" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all,
>
> Long story short, is it possible in VBA to dynamically select cells in
> the range (for example) A1:Mx where x = the last row that contains data
> (i.e. non-blank cells). The blank cells (i.e. those cells after row x)
> do contain formulas linked to other worksheets but if the relevant
> cells in those other worksheets are blank then so too are these cells.
>
> Thanks in advance
>
>
> --
> AJMorgan591
> ------------------------------------------------------------------------
> AJMorgan591's Profile:
http://www.excelforum.com/member.php...o&userid=27774
> View this thread: http://www.excelforum.com/showthread...hreadid=473647
>