Automatically remove oldest record when a new record is added

[Denny57]

I am considering using a table (or simple worksheet) in Excel to maintain records of the hours worked by a small group of employees over a period of 52 weeks. I will be adding one new line of records each week and I am looking for a way to remove the first row of information (oldest) as each new row of information is added. I will then use this to calculate the average number of hours worked by each employee based on the number of hours / actual weeks they worked during this period.

I can manage the calculations but would welcome assistance with either VBA Code or a Macro that will maintain just 52 rows of information in a table.

Thank you in advance

 

[Peter_SSs]

Assuming that you use a formal Excel table and that it is the only formal table on the worksheet. You could try this Worksheet_Change event macro. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ListObjects(1)
    If Not Intersect(Target, .DataBodyRange) Is Nothing Then
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Do Until .DataBodyRange.Rows.Count <= 52
        .ListRows(1).Delete
      Loop
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End If
  End With
End Sub

 

[Denny57]

Sorry for the delay, thanks this works perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.