after entering data in last row delete 1st row

[Guest]

Hi,
I have 53 rows on my worksheet (52 weeks in the year + 1 week), I update the
information weekly. How can I enter the current week and always have 53 rows
(no more and no less). I only want to see the past year plus one week.

Thanks for any help I can get.

 

[Guest]

The only difficulty I see is having Excel know when to delete the first row:
you need to make sure you have finished entering all the data on the new row,
I would presume.

So let's say you need to fill in the first 10 columns (A-J) - you could do
the following :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DataRows As Integer, NewLine As Range

DataRows = Range("A1").CurrentRegion.Rows.Count ' gets the number of rows
Set NewLine = Range("A" & DataRows & ":J" & DataRows) ' points to the last row

If (DataRows > 53) And (WorksheetFunction.CountA(NewLine) = 10) Then
Range("A1").EntireRow.Delete

End Sub