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?

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?

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?