Excel/VBA question

[Ramy27]

I have a question. The answer may be in Excel, if not, I need create VB function to do this.

When I type in a excel range (eg, A2:E34) in an Excel cell it should count the number of rows and columns in this range and write the results in another excel cell(s). Is it possible?



---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[SkipVought]

Hi,

You can use the spreadsheet function COUNTA(). For your example...
[tt]
column count: =COUNTA(2:2)
row count: =COUNTA(A:A)
[/tt]


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

...that is assuming that each cell in the range in row 2 contains values and each cell in the range in column A contains values.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Ramy27]

Thanks. But that is not my question.

What the user will actually enter in the excel CELL is something like the following

A150

Then I need two functions in different cells that work out the number of rows and columns...*so the answers should be 50 and 4

---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[xlbo]

Have you actually tried looking in the help files ????

Took me about 5 seconds to find an appropriate function (ROWS) and then find a way to turn the string in the cell to a range reference

the answer you are looking for is

=ROWS(INDIRECT(A1))
=COLUMNS(INDIRECT(A1))

where "A150" is in cell A1

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Use Split function to divide the regerences.

analyse each referenc string to get the APLHA and NUMERIC parts. Use cell references to get the difference in COLUMNS like

NbrCols = Cells(1, SecondAlphaRef).Column - Cells(1, FirstAlphaRef).Column + 1

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Skip - I think I prefer mine

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Lets duke it out! ;-)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Given your love of word games, you can count on it

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

but FIRST, we gotta quaff a pint or two!

... and then, two being somewhat approximate, the count could ent up pr't' near anything!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Ramy27]

thanks. That's exactly what i was looking for.

---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[xlbo]

LOL

As long as we leave on spea-King terms and get on royally, it'll all be fine. Having said that, maybe we should take this off to the squire-ing the circle forum.....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I'll leave footprince as I tread thusward.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}