I think I'd reconsider how the named ranges are defined. How do you
determine which rows are included in the first range on Period 1's sheet?
If there is some common way to determine this, when you delete the rows, you
can redefine the ranges.

" wrote:

Hi - Is there a work-around for this? Teachers are using a spreadsheet
for grading students and would like the ability to delete a student.
A student record occupies 2 rows; records start on row 13; lastnames
are in every odd B col, firstnames are in every odd C col. I needed
to make named ranges, but ran into Excel's range size limitations.
So, I made 4 named ranges per sheet (example: Period 1's ranges are
below). When I add the below code to delete a student's 2 rows, this
impacts the named ranges, creating #REF! in the affected range.

=========================

Sub DeleteBlock() 'code to delete a student's rows
If ActiveCell.Row = 13 Then
Application.EnableEvents = False
ActiveCell.Resize(2).EntireRow.Delete
End If
Application.EnableEvents = True
End Sub

=========================
Here are the named ranges on Sheet 1:

Range11 '1st range on Period 1's sheet
='Period 1'!$AV$13:$FA$13,'Period 1'!$AV$15:$FA$15,'Period 1'!$AV
$17:$FA$17,'Period 1'!$AV$19:$FA$19,'Period 1'!$AV$21:$FA$21,'Period
1'!$AV$23:$FA$23,'Period 1'!$AV$25:$FA$25,'Period 1'!$AV$27:$FA$27

Range12 '2nd range on Period 1's sheet
='Period 1'!$AV$29:$FA$29,'Period 1'!$AV$31:$FA$31,'Period 1'!$AV
$33:$FA$33,'Period 1'!$AV$35:$FA$35,'Period 1'!$AV$37:$FA$37,'Period
1'!$AV$39:$FA$39,'Period 1'!$AV$41:$FA$41,'Period 1'!$AV$43:$FA$43

Range13 '3rd range on Period 1's sheet
='Period 1'!$AV$45:$FA$45,'Period 1'!$AV$47:$FA$47,'Period 1'!$AV
$49:$FA$49,'Period 1'!$AV$51:$FA$51,'Period 1'!$AV$53:$FA$53,'Period
1'!$AV$55:$FA$55,'Period 1'!$AV$57:$FA$57,'Period 1'!$AV$59:$FA$59

===========================

EXAMPLE PROBLEM: removing a student causes the affected range to do
this:

='Period 1'!#REF!,'Period 1'!$AV$25:$FA$25,'Period 1'!$AV$27:$FA
$27,'Period 1'!$AV$29:$FA$29,'Period 1'!$AV$31:$FA$31,'Period 1'!$AV
$33:$FA$33,'Period 1'!$AV$35:$FA$35,'Period 1'!$AV$37:$FA$37

Basically, we wanted the spreadsheet to delete the student's rows,
then bring up any student rows that were beneath that student's 2
rows, so we didn't have a gap in the sheet.

Thanks for any and all help.