Trying to create a range dynamically

  • Thread starter Thread starter Tom O'Brien
  • Start date Start date
T

Tom O'Brien

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
 
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
Click to expand...

Is the data in an array? My first guess would be a nested loop: the
inner one to go across the columns and the outer one to go down the
rows...
For i = 0 To noOfRows
For j = 0 To noOfColumns
'check for empty string to start and to start (presumably with a
boolean)
'keeping track of where you started and where you stopped; then
return that
Next
Next

Depending on what you need you could just return the formatted string
"A3:D5" (or would it be C5 in the example?), though I'd prefer passing
back an object with start and end properties: objGrid.Start.Column =
0, objGrid.Start.Row = 2, objGrid.End.Column = 2, etc. (if zero-based,
otherwise, 1 and 3; 3 and 5)
 
Back
Top