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.

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,

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

jesser-ga
rated this answer:
and gave an additional tip of:
$5.00

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.

Comments

There are no comments at this time.

Important Disclaimer:
Answers and comments provided on Google Answers are general
information, and are not intended to substitute for
informed professional medical, psychiatric, psychological, tax,
legal, investment, accounting, or other professional
advice. Google does not endorse, and expressly disclaims
liability for any product, manufacturer, distributor,
service or service provider mentioned or any opinion
expressed in answers or comments. Please read carefully the
Google Answers Terms of Service.

If you feel that you have found inappropriate content,
please let us know by emailing us at
answers-support@google.com
with the question ID listed above. Thank you.