Getting excel array dimension

[banavas]

Dear Friends,

when having a excel sheet with values from a1 to c5, is it possible to
get the dimensions of the 2-dim array without counting?

Thanks,
Georgios

 

[Guest]

All Excel ranges have a rows and columns property; these are effecively your dimensions

e.

?range("c4:f19").rows.coun
16
?range("c4:f19").columns.coun
4
You have to take care when indexing i.e. allow for option base.

 

[banavas]

*All Excel ranges have a rows and columns property; these ar
effecively your dimensions.

e.g

?range("c4:f19").rows.count
16
?range("c4:f19").columns.count
4
You have to take care when indexing i.e. allow for option base. *

Yes that is clear. The question though arised from the fact that when
pivot table is to be created with the wizard, Excel magically enoug
automatically selects the range that appears on the sheet when only on
cell of the range is selected.
That means that it can found the U & L bounds of the range.

In my application the input range may vary in terms of dimensions and
want to avoid manually to select, give or calculate the dimensions o
my range.

Thanks,
Georgio

 

[JE McGimpsey]

One way:

?Range("C4").CurrentRegion.Rows.Count

?Range("C4").CurrentRegion.Columns.Count