Keep only 12 rows of data in an excel table

[Denny57]

In order to calculate holiday entitlement, I am hoping that I can create either a table or database which only contains 12 lines of information. As each new line of data is added, I am hoping to automatically remove the information in the first row of data

Table Format
Row 1 - Row Headers
Row 2 - Week 1 Data
Row 3 - Week 2 Data
.....
Row 13 - Week 12 Data

Once data is entered into Row 14 , I would like to run a macro which would delete the information in Row 2 making Row 3 into Row 2. This would continue until Row 14 becomes Row 13

I will then be able to run formulae to extract the information I need to maintain the records I need to extract

This may not be the most effective solution as staff will be added and changed periodically, but will help until I can create a better solution

All help will be most welcome

 

[Ezguy4u]

Denny let's get the ball rolling. Below is my solution to your problem. Now this usually generates more questions than answers but we have to start somewhere. If we are lucky an A student will weigh in and will do the same thing with 2 lines of code. Let's start the discussion.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim InterSectRange As Range

Set WatchRange = Range("B15")

Set InterSectRange = Intersect(Target, WatchRange)
If InterSectRange Is Nothing Then

Else

Application.EnableEvents = False
Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
Application.EnableEvents = True
End If

End Sub

24-01-20 rev a.xlsm
	A	B
1	Week	Data
2	Week 1	Data 1
3	Week 2	Data 2
4	Week 3	Data 3
5	Week 4	Data 4
6	Week 5	Data 5
7	Week 6	Data 6
8	Week 7	Data 7
9	Week 8	Data 8
10	Week 9	Data 9
11	Week 10	Data 10
12	Week 11	Data 11
13	Week 12	Data 12
14	Week 13	Data 13
Sheet1

 

[Peter_SSs]

I can create either a table

Would that be a formal Excel table (ListObject) created for example through the Insert ribbon tab -> Table or just a range of cells that you are referring to as a table?

If it is a formal Excel table then you could consider the suggestion in this post, changing the "52" in that code to "12"
That suggestion doesn't require you to "run" anything as such. As soon as there are more than 12 rows in the table rows will automatically be deleted from the top until just 12 remain.

 

[Denny57]

Would that be a formal Excel table (ListObject) created for example through the Insert ribbon tab -> Table or just a range of cells that you are referring to as a table?

If it is a formal Excel table then you could consider the suggestion in this post, changing the "52" in that code to "12"
That suggestion doesn't require you to "run" anything as such. As soon as there are more than 12 rows in the table rows will automatically be deleted from the top until just 12 remain.

Thanks, works fine

 

[Peter_SSs]

Thanks, works fine

Cheers, glad it worked for you. Thanks for the follow-up.