6

#### 6afraidbecause789

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.