Determining counts of numbers of used cells

[Sam Hobbs]

In Excel, is there a way to determine the number of rows and columns that
are actually used? In other words, the maximum row number and column number
that contains anything. I need this to optimize searches of a worksheet so
that (VBA) macros don't need to search beyond where it is needed to search.

I am sure this question has been asked many times before but I don't know
what terminology to use to search for. I have looked all through the Excel
documentation without success.

 

[Karl E. Peterson]

In Excel, is there a way to determine the number of rows and columns that
are actually used? In other words, the maximum row number and column number
that contains anything.

Would ActiveWindow.ActiveSheet.UsedRange work?

Later... Karl

 

[Sam Hobbs]

Would ActiveWindow.ActiveSheet.UsedRange work?

Yes, that works. I don't know why I could not find it; probably because the
term "range" is not as meningful to me as it is to Excel.

If there is such a thing as a Cell object, then the help does not help to
find it. I know that does not prove it doesn't exist, because if I put
"rows" into the Index or the Answer Wizard, I don't get the Rows property
either. The Answer Wizard is stupid. Probably there is not such a thing as a
Cell object, and I just can't get it in my head that a cell is a range.

I must continually remind myself that Microsoft intentionally uses confusing
terminology. That helps me defeat them in doing that.

 

[Karl E. Peterson]

Hi Sam --

Yes, that works. I don't know why I could not find it; probably because the
term "range" is not as meningful to me as it is to Excel.

It seems *everything* in Excel revolves around ranges! Took a few hammer-blows to

If there is such a thing as a Cell object, then the help does not help to
find it.

I haven't done a lot in Excel, myself, but I don't think there is. A cell is simply
a 1x1 range, I believe.

I know that does not prove it doesn't exist, because if I put
"rows" into the Index or the Answer Wizard, I don't get the Rows property
either. The Answer Wizard is stupid. Probably there is not such a thing as a
Cell object, and I just can't get it in my head that a cell is a range.

Well, if it helps, a Range does have a Cells property. <g>

Or, better yet, how's this:

Dim Cell As Range

I must continually remind myself that Microsoft intentionally uses confusing
terminology. That helps me defeat them in doing that.

You are now solidly on the road to recovery, yessir!

Later... Karl