Deleting rows messes up Named Ranges--How can this be fixed?

[6afraidbecause789]

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.

 

[Barb Reinhardt]

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.

 

[6afraidbecause789]

There are headings and info at the top of every sheet, from rows 1 to
12. All students and their data are entered beginning on row 13.
Each student's data and associated formulas will occupy 2 rows, so
students' names will always be on odd rows. They are not sorted in any
particular order (class sizes average around 10 students). However,
some students will be leaving midway through a semester; hence, the
reason why we wanted a way to delete. I'm rather new to automating
things in VB.

 

[Don Guillett]

Instead of a13 try something like this.
=offset($a$12,1,0,counta($a:$a)-12,1)
I also suggest not 2 rows for data but 2 COLUMNS.

 

[Barb Reinhardt]

So what are the named ranges for? A class? How can we determine the
groupings in each named range from the data on the sheet? If there is a way
to do that, something can be written in VBA. If not, you're out of luck.

 

[Barb Reinhardt]

Let me ask this question again. Does the data on one sheet apply to one
class? If the last row were say ... 57, the named range you want to work
with would include data in AV13:FA13 in every odd row to AV57:FA57? If so,
I think this can be done programmatically. Let's assume that there is data
in column C in the last row you want to use.

You could use code like this to define the range with every row that may
have data in it like this:

Sub Test()
Dim aWS As Excel.Worksheet
Dim aWB As Excel.Workbook
Dim lRow As Long
Dim myRange As Excel.Range
Dim i As Long

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet
lRow = aWS.Cells(aWS.Rows.Count, "B").End(xlUp).Row


If lRow < 15 Then Exit Sub

Set myRange = aWS.Range("$AV$13:$FA$13")
Set myRange = myRange.Offset(0, 0).Resize(lRow - 13 + 1,
myRange.Columns.Count)
Debug.Print myRange.Address

aWB.Names.Add Name:="myName", RefersTo:="=" & myRange.Address


End Sub

If you are using the range within code elsewhere, you could pull out the odd
rows like this

dim r as excel.range
Dim myRange as excel.range
dim myNewRange as excel.range

for each r in myrange
if r.row mod 2 = 1 then
if mynewrange is nothing then
Set myNewRange = r
else
Set myNewRange = union(myNewRange,r)
end if
end if
next r

next r

HTH,
Barb Reinhardt

 

[6afraidbecause789]

Hi Barb and Don,
Sorry for leaving the thread awhile; needed travel and sleep. The
data on a sheet applies to one class. The named ranges are for a
Worksheet_Change event that programmatically validates possible
combinations of scores past column AR. I only formatted the sheets to
extend down to row 60; this would more than hold the max number of
students + additional students for a class in a semester. And, yes,
columns would've been nice; a database even nicer, but making the data
go horizontal would've run into Excel's # of column limitation.
Thanks again.

 

[Don Guillett]

If desired, send your wb to my address below along with snippets of these
msgs inserted into a worksheet and before/after examples.

 

[Don Guillett]

'=OFFSET(Don!$A$5,1,0,1,3),OFFSET(Don!$A$5,3,0,1,3)