I'm using Excel 2000 and I need some way to automatically define a
worksheet's range (e.g. a1...m404) as well as number of rows (e.g.
404)

I have a macro written now that manipulates a worksheet, but it only
works if the sheet is 404 rows. I did this by using the record feature
and the navigation keys, but the record feature fills in the actual
number. So, the next worksheet I used the macro again figured it was
404 rows.

I need to be able to accommodate a worksheet with any number of rows.

---

Request for Question Clarification by maniac-ga on 07 Aug 2005 20:41 PDT

Hello Jesser,

I need a little more information before posting a full and complete answer.

There are at least three methods in Excel to "determine a range of
cells" and the number of rows in a worksheet:
 - the "current region"
 - the "last cell"
 - the "current array"

The "current region" refers to the range of rows / columns without
blank rows or columns. I am guessing you want this but cannot be sure
from your explanation.

The "last cell" refers to the last cell w/ any data and/or formatting.

The "current array" refers to a set of cells in an "array formula";
this is pretty infrequently used but I am listing it for completeness.

If you use the menu
  Edit -> Go to -> Special...
and select each of these, you can see how they work. Please indicate
which one you need (or let me know if you need more than one
explained).

Also, could you post a couple of the key lines (those that refer to
the 404 rows) from the Visual Basic macro in a clarification so I can
give you the best way to change them to do what you want.

  --Maniac

---

Clarification of Question by jesser-ga on 08 Aug 2005 09:06 PDT

Hi -

Option A worked for me. Using your steps, I was able to get around the issue.

Thanks,

---

Request for Question Clarification by nenna-ga on 08 Aug 2005 11:17 PDT

So, would you like the researcher to post his/her suggestion as an
answer since it did answer your question?

Nenna-GA

---

Clarification of Question by jesser-ga on 08 Aug 2005 16:19 PDT

Sure. Is there some process where I accept the answer so the paerson
gets paid? Sorry for delay in responding, I've had intermittant
internet connections and resorting to dialin.

---

Request for Question Clarification by nenna-ga on 08 Aug 2005 17:15 PDT

Hi Jesser,

No, you don't have to do anything. I've made an attempt to notify
Maniac to let them know you wanted their clarification as an answer.
Hopefully they'll show up, post an official answer in the answer box,
and it'll all be taken care of :)

Nenna-GA

Hello Jesser,

Glad to help.

Let me recap the options you have to adjust the work to be performed
based on the number of rows in a worksheet. From before, we have:
 - the "current region" (which appears to be what you wanted)
 - the "last cell"
and
 - the "current array"

Current Region:
In Visual Basic, you can expand the selection to the current region by
something like:
  Selection.CurrentRegion.Select
or you can determine the number of rows in the current region with something like
  CurRows = Selection.CurrentRegion.Rows.Count
or work through all the cells in a region using a loop like this (from
the Excel online help)
    For Each c In ActiveCell.CurrentRegion.Cells
        If Abs(c.Value) < 0.01 Then c.Value = 0
    Next
(which zeros cells which have absolute value less than 0.01)

Last Cell:
In Visual Basic, you can refer to the "last cell" of a spreadsheet
using a statement like:
    LastRow = Selection.SpecialCells(xlCellTypeLastCell).Row
which looks up the row of the last cell and assigns it to LastRow. You
could then create a for loop like
  For I = 1 to LastRow
    ' work on each row
  Next I
to walk through each row.

Current Array:
If you use array formulas, you can use "CurrentArray" to refer to the
array (in a way similar to CurrentRegion).

A few other techniques I have found helpful when working with macros
to determine a "better way" to solve the problems.
 - use the Excel menu Tools -> Macros -> Record New Macro ...
and then record a few steps to copy / paste (and usually edit) into
what I need in the macro I am writing.
 - use the Visual Basic menu View -> Object Browser
which brings up a window that can be used to search for methods and
properties. For example, enter region in the search field and then
select an item / click on the "?" button to get help.
 - use the Visual Basic menu View -> Immediate Window
to bring up a window to enter statements like
  print Selection.SpecialCells(xlCellTypeLastCell).Row
(which printed 4 on my test spreadsheet)
to check if the code fragments I have will work
 - use the single step / immediate window to look at values if a macro
does not behave properly.

Please make a clarification request if some part of the answer is
unclear or if you want some part of the answer expanded more fully.

  --Maniac

I'm certain Maniac's answer would have been even better if I had the
opportunity to properly answer the first question. Given I couldn't,
maniac has done a great job of hitting the high notes.