automatic macro update

[boconnell]

is there a way to get a macro to update automatically?
i have a training spreadsheet for 50 employees, that has a macro assigned to
it. the macro checks the training dates in cells, and highlights the cells
when training is due. the macro is written to address a range of rows for
the 50 employees, but when new employees are added, the macro must be updated
every time, in WAY too many areas.
is there a way to link the row numbers in the macro to the row numbers in
the spreadsheet, so that the macro is updated automatically when the number
of rows changes?

 

[Myrna Larson]

The macro should be written to use a named range instead of hard-coded ranges,
i.e.

Worksheets("Sheet1").Range("EmployeeList")

rather than

Worksheets("Sheet1").Range("A1:K100")

To make this work when you change the number of employees, the range name can
be defined as a dynamic name, i.e.

=OFFSET($A$2,0,0,COUNTA($A$2:$A$5000),11))

The above assumes that the range occupies 11 columns, A:K, and there are no
embedded blank cells in column A. If necessary, change the 5000 to some number
greater than the maximum possible number of employees.

If you define the name that way, you can add new employees at the bottom of
the list. OTOH, if you define the original name as referring to $A$2:$K$35,
then you would have to INSERT a new row between 2 and 35 so the name would
expand to include it.

 

[Myrna Larson]

PS: Another way, if there are embedded blank cells, you can find the last used
row like this:

Dim Rng As Range
Dim R As Long

With Worksheets("Sheet1")
R = .Cells(.Rows.Count, 1).End(xlUp).Row
'assume range begins in row 2, has 11 columns
Set Rng = .Cells(2, 1).Resize(R - 1, 11)
End With

In the rest of your code, you would refer to the variable Rng.

 

[boconnell]

you're a goddess myrna!!...............it's not all that i was hoping for,
but it works for what i need. thanks!

 

[Myrna Larson]

What else were you hoping for? I think I gave you what you ASKED for <g>.

You mentioned having to do this in multiple places (in the code, I assume). If
so, if you use the Named Range approach, you could use search and replace to
replace, say, $A$1:$K$50, with the range name. If you set a range variable,
you could put that code in a separate function, and call it from each of your
other routines, i.e.

Function GetEmployeeRange() As Range
Dim R As Long
With Worksheets("Sheet1")
R = .Cells(.Rows.Count, 1).End(xlUp).Row
'assume range begins in row 2, has 11 columns
Set GetEmployeeRange = .Cells(2, 1).Resize(R - 1, 11)
End With
End Function

In your other routines, use code like

Dim Emp As Range
Set Emp = GetEmployeeRange()