Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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. " wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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 " wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows messes up Named Ranges--How can this be fixed?
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 Microsoft MVP Excel SalesAid Software wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting multiple named ranges | Excel Discussion (Misc queries) | |||
problem in deleting the named ranges | Excel Worksheet Functions | |||
Deleting Named Ranges | Excel Worksheet Functions | |||
Deleting Named Ranges | Excel Programming | |||
Deleting many named ranges | Excel Worksheet Functions |