dynamic range in a macro

[MJKelly]

Hello,

I am using a set range in numerous macros and I anticpate the size of
the range changing over time, ie ("A1:A10") may become ("A1:A35"). Is
there a way by which I can have the spreadsheet run a check of the
size of certain set of data (row and column numbers), and name the
range so it can be referenced in the macros?

I was thinking of setting the name as "StaffNames" and as more staff
are employed, the size of the range will increase.

The macros simply loop through the range identifying certain criteria
and then carry out an action such as changing background colours or
counting certain occurancies.

I have heard of named ranges but have never set one up. Would a named
range solve the issue I have described and if so, how would I do that?

Matt

 

[Guest]

You probably want a dynamic named range. That being the case here is a
reference for you...

http://www.cpearson.com/excel/named.htm

 

[Don Guillett]

This would be self adjusting.

Insert>name>define>name it colA>in the refers to box, type
=offset("$a$1,0,0,counta($a:$a),1)
enter