Need to dynamically choose Ranges of non-empty objects
 

hello,

This is a basic questions... i have just started using vb....

I want to figure out how to create a range dynamically.

Say I have some data in a variable number of columns, in this example
3 - A, B, & C

Eg.

A B C
1
2
3 2 2 2
4 3 3 3
5 4 4 4

I want to write a sub/function that returns a range that contains all
the non-empty values in those columns.
I will know where the non-empty values begin, so all I need to find
out is the last non-empty cell, and create
a range out of this.

The range I want in this instance is: A3:D5

Can anyone help with this?

T

Need to dynamically choose Ranges of non-empty objects
 

Hi Tom

Maybe you can use

Range("A3").CurrentRegion.Select

If you want to find the last cell in a range you can also look at this
http://www.rondebruin.nl/last.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tom O'Brien" wrote in message oups.com...
hello,

This is a basic questions... i have just started using vb....

I want to figure out how to create a range dynamically.

Say I have some data in a variable number of columns, in this example
3 - A, B, & C

Eg.

A B C
1
2
3 2 2 2
4 3 3 3
5 4 4 4

I want to write a sub/function that returns a range that contains all
the non-empty values in those columns.
I will know where the non-empty values begin, so all I need to find
out is the last non-empty cell, and create
a range out of this.

The range I want in this instance is: A3:D5

Can anyone help with this?

T

Need to dynamically choose Ranges of non-empty objects
 

set rng = Range("A3").currentRegion
msgbox rng.Address

--
Regards,
Tom Ogilvy


"Tom O'Brien" wrote:

hello,

This is a basic questions... i have just started using vb....

I want to figure out how to create a range dynamically.

Say I have some data in a variable number of columns, in this example
3 - A, B, & C

Eg.

A B C
1
2
3 2 2 2
4 3 3 3
5 4 4 4

I want to write a sub/function that returns a range that contains all
the non-empty values in those columns.
I will know where the non-empty values begin, so all I need to find
out is the last non-empty cell, and create
a range out of this.

The range I want in this instance is: A3:D5

Can anyone help with this?

T