Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not Sequencing
I have a worksheet that contains (5) rows as a default (B21 thru B25). If the
user needs more rows, he left clicks on an icon which adds another three rows to the existing (5) rows (B21 thru B28). If the user hits the icon again, another (3) rows are added. In addition to adding (3) rows, the first new row looks at the last existing row to pick up on the last ID number which identifies one row from the other. As an example, if the last existing row ID was 01.005, when the user adds another (3) rows, the next new row ID will be 01.006 followed by 01.007 and then 01.008. In addition to the ID column updating the row sequence, I also have other columns that also pass on their values to the next new row. To accomplish this, if the last row was B25, the new row will be programmed to look at the cell above and add 1 to it, as an example of the ID column. Finally the problem. If I have a worksheet in which multiple rows were added, let's say (25) and the new row length is from B20 to B45 and I decide to delete some or any rows rows between B20 and B45, I receive a #REF! fault that starts at the row just below the first row I chose to delete all the way down to the end. What can I do to make sure that when any row or groups of rows are deleted, the data from the existing row left will be copied to the rows below? Any help would be appreciated. Thank You, JAD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not Sequencing
JAD,
These seem like the cells are based on formulas, similar to A1: 1, A2: A1+1. With that said, if you delete a row, the row below the deleted will need the formulas reassigned with the correct Formula. So, if you delete row 2, you will need row 3 to reassign the formulas to row 1. A1: 1 A2: A1+1 (2) A3: A2+1 (3) Delete Row 2: A1: 1 A3: A2+1 (3) will need to change to A2: A1+1 (2) Only the one row will need to be changed, it will affect all rows below it. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "JAD" wrote: I have a worksheet that contains (5) rows as a default (B21 thru B25). If the user needs more rows, he left clicks on an icon which adds another three rows to the existing (5) rows (B21 thru B28). If the user hits the icon again, another (3) rows are added. In addition to adding (3) rows, the first new row looks at the last existing row to pick up on the last ID number which identifies one row from the other. As an example, if the last existing row ID was 01.005, when the user adds another (3) rows, the next new row ID will be 01.006 followed by 01.007 and then 01.008. In addition to the ID column updating the row sequence, I also have other columns that also pass on their values to the next new row. To accomplish this, if the last row was B25, the new row will be programmed to look at the cell above and add 1 to it, as an example of the ID column. Finally the problem. If I have a worksheet in which multiple rows were added, let's say (25) and the new row length is from B20 to B45 and I decide to delete some or any rows rows between B20 and B45, I receive a #REF! fault that starts at the row just below the first row I chose to delete all the way down to the end. What can I do to make sure that when any row or groups of rows are deleted, the data from the existing row left will be copied to the rows below? Any help would be appreciated. Thank You, JAD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not Sequencing
Thomas:
I forgot to mention one important question; How do I create a macro that will recreate the formula back to what it was prior to the deletion? Your interpretation is exactly what is happening. Unfortunately, I don't want the end user to modify the worksheet. Is it possible? Also, should I have the end user specify the rows to be deleted and then run a macro that deletes and repairs? Any help would be appreciated "Thomas [PBD]" wrote: JAD, These seem like the cells are based on formulas, similar to A1: 1, A2: A1+1. With that said, if you delete a row, the row below the deleted will need the formulas reassigned with the correct Formula. So, if you delete row 2, you will need row 3 to reassign the formulas to row 1. A1: 1 A2: A1+1 (2) A3: A2+1 (3) Delete Row 2: A1: 1 A3: A2+1 (3) will need to change to A2: A1+1 (2) Only the one row will need to be changed, it will affect all rows below it. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "JAD" wrote: I have a worksheet that contains (5) rows as a default (B21 thru B25). If the user needs more rows, he left clicks on an icon which adds another three rows to the existing (5) rows (B21 thru B28). If the user hits the icon again, another (3) rows are added. In addition to adding (3) rows, the first new row looks at the last existing row to pick up on the last ID number which identifies one row from the other. As an example, if the last existing row ID was 01.005, when the user adds another (3) rows, the next new row ID will be 01.006 followed by 01.007 and then 01.008. In addition to the ID column updating the row sequence, I also have other columns that also pass on their values to the next new row. To accomplish this, if the last row was B25, the new row will be programmed to look at the cell above and add 1 to it, as an example of the ID column. Finally the problem. If I have a worksheet in which multiple rows were added, let's say (25) and the new row length is from B20 to B45 and I decide to delete some or any rows rows between B20 and B45, I receive a #REF! fault that starts at the row just below the first row I chose to delete all the way down to the end. What can I do to make sure that when any row or groups of rows are deleted, the data from the existing row left will be copied to the rows below? Any help would be appreciated. Thank You, JAD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not Sequencing
JAD,
Something list this should work for you. Tweak it a little for your needs, but its a start. You could add a button to your Excel sheet to run this if you wanted. This will only delete one row at a time. Sub Delete_Row() Dim dRow As Long Dim sCol As String Dim eCol As String dRow = InputBox("Enter Row Number to Delete", "Delete Row") 'Change for your Columns sCol = "A" eCol = "H" For c = 1 To Range(sCol & dRow & ":" & eCol & dRow).Count 'Amend Formula if needed If Cells(dRow, c).FormulaR1C1 = "=R[-1]C+1" Then Cells(dRow + 1, c).FormulaR1C1 = "=R[-2]C+1" End If Next Rows(dRow & ":" & dRow).Delete End Sub -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "JAD" wrote: Thomas: I forgot to mention one important question; How do I create a macro that will recreate the formula back to what it was prior to the deletion? Your interpretation is exactly what is happening. Unfortunately, I don't want the end user to modify the worksheet. Is it possible? Also, should I have the end user specify the rows to be deleted and then run a macro that deletes and repairs? Any help would be appreciated "Thomas [PBD]" wrote: JAD, These seem like the cells are based on formulas, similar to A1: 1, A2: A1+1. With that said, if you delete a row, the row below the deleted will need the formulas reassigned with the correct Formula. So, if you delete row 2, you will need row 3 to reassign the formulas to row 1. A1: 1 A2: A1+1 (2) A3: A2+1 (3) Delete Row 2: A1: 1 A3: A2+1 (3) will need to change to A2: A1+1 (2) Only the one row will need to be changed, it will affect all rows below it. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "JAD" wrote: I have a worksheet that contains (5) rows as a default (B21 thru B25). If the user needs more rows, he left clicks on an icon which adds another three rows to the existing (5) rows (B21 thru B28). If the user hits the icon again, another (3) rows are added. In addition to adding (3) rows, the first new row looks at the last existing row to pick up on the last ID number which identifies one row from the other. As an example, if the last existing row ID was 01.005, when the user adds another (3) rows, the next new row ID will be 01.006 followed by 01.007 and then 01.008. In addition to the ID column updating the row sequence, I also have other columns that also pass on their values to the next new row. To accomplish this, if the last row was B25, the new row will be programmed to look at the cell above and add 1 to it, as an example of the ID column. Finally the problem. If I have a worksheet in which multiple rows were added, let's say (25) and the new row length is from B20 to B45 and I decide to delete some or any rows rows between B20 and B45, I receive a #REF! fault that starts at the row just below the first row I chose to delete all the way down to the end. What can I do to make sure that when any row or groups of rows are deleted, the data from the existing row left will be copied to the rows below? Any help would be appreciated. Thank You, JAD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmable Column Sequencing? | Excel Discussion (Misc queries) | |||
automatic number sequencing? | Excel Worksheet Functions | |||
sequencing numbers | Excel Programming | |||
Sequencing of projects | Excel Discussion (Misc queries) | |||
Row Sequencing | Excel Worksheet Functions |