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.