Macro to Number New Rows Inserted Into Table?
 

The macro below shows all the rows on a sheet with an AutoFilter and then
inserts a row at the Active Cell:

Sub ShowAllRecordsAndInsertRows()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

With ActiveCell
Selection.EntireRow.Insert

End With
End Sub

In the first column (A) of the sheet, however, I have each row numbered from
one to whatever the last record is (currently it's 7699) so that I can always
restore the sheet to its original order easily. How can I augment my macro so
that it also inputs a number in the first cell of the new row that is between
the numbers above and below it?

I figure the easiest way is for the macro to add the values in the adjacent
cells in column A over and under the new row, divide that sum by two, and
return that value into the cell in column A in the new row. (It doesn't
really matter what the numbers in column A are as long as they reflect the
correct original order of the table).

I'm a novice at VBA, so I have no idea how to do this. Could anyone please
help?

Macro to Number New Rows Inserted Into Table?
 

Lysander,

Sub ShowAllRecordsAndInsertRows2()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

ActiveCell.EntireRow.Insert

Cells(ActiveCell.Row, 1).Value = _
(Cells(ActiveCell.Row - 1, 1).Value + _
Cells(ActiveCell.Row + 1, 1).Value) / 2

End Sub


HTH,
Bernie
MS Excel MVP


"Lysander Stark" wrote in message
...
The macro below shows all the rows on a sheet with an AutoFilter and then
inserts a row at the Active Cell:

Sub ShowAllRecordsAndInsertRows()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

With ActiveCell
Selection.EntireRow.Insert

End With
End Sub

In the first column (A) of the sheet, however, I have each row numbered from
one to whatever the last record is (currently it's 7699) so that I can always
restore the sheet to its original order easily. How can I augment my macro so
that it also inputs a number in the first cell of the new row that is between
the numbers above and below it?

I figure the easiest way is for the macro to add the values in the adjacent
cells in column A over and under the new row, divide that sum by two, and
return that value into the cell in column A in the new row. (It doesn't
really matter what the numbers in column A are as long as they reflect the
correct original order of the table).

I'm a novice at VBA, so I have no idea how to do this. Could anyone please
help?

Macro to Number New Rows Inserted Into Table?
 

Bernie:

Thank you so much--this does exactly what I needed it to do. I really
appreciate your help! You've made the rest of this day a lot easier!

"Bernie Deitrick" wrote:

Lysander,

Sub ShowAllRecordsAndInsertRows2()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

ActiveCell.EntireRow.Insert

Cells(ActiveCell.Row, 1).Value = _
(Cells(ActiveCell.Row - 1, 1).Value + _
Cells(ActiveCell.Row + 1, 1).Value) / 2

End Sub


HTH,
Bernie
MS Excel MVP


"Lysander Stark" wrote in message
...
The macro below shows all the rows on a sheet with an AutoFilter and then
inserts a row at the Active Cell:

Sub ShowAllRecordsAndInsertRows()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

With ActiveCell
Selection.EntireRow.Insert

End With
End Sub

In the first column (A) of the sheet, however, I have each row numbered from
one to whatever the last record is (currently it's 7699) so that I can always
restore the sheet to its original order easily. How can I augment my macro so
that it also inputs a number in the first cell of the new row that is between
the numbers above and below it?

I figure the easiest way is for the macro to add the values in the adjacent
cells in column A over and under the new row, divide that sum by two, and
return that value into the cell in column A in the new row. (It doesn't
really matter what the numbers in column A are as long as they reflect the
correct original order of the table).

I'm a novice at VBA, so I have no idea how to do this. Could anyone please
help?