dynamic range in a macro
 

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

dynamic range in a macro
 

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

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

Jim Thomlinson


"MJKelly" wrote:

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

dynamic range in a macro
 

This would be self adjusting.

Insertnamedefinename it colAin the refers to box, type
=offset("$a$1,0,0,counta($a:$a),1)
enter
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MJKelly" wrote in message
...
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