Not Sequencing

[JAD]

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

 

[Thomas [PBD]]

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.

 

[JAD]

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

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.

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

 

[Thomas [PBD]]

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.

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

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.

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