Deleting a row when adding a new row - - baffling!

  • Thread starter Thread starter rowlo-efc
  • Start date Start date
R

rowlo-efc

I have a spreadsheet that contains 100 rows. I want to limit the
number of rows to 100. I.e. if the user adds a row (i.e. row 101) then
all data from row 1 is deleted. I do not have to worry about the
deleted data as it is updated in the new row!
Can anybody help.
 
I think you're saying you want the first row to be automatically deleted when a row is added
at the end of the table. The row will have to be deleted, it won't happen automatically.
The macro language is there for such specific requirements.
 
Right click sheet tab>view code>copy/paste this. Now when col a has more
than 100 rows the second row will go.
row 2 assumes that you have a header in row 1 that you want to keep.

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(Rows.Count, 1).End(xlUp).Row > 100 Then Rows(2).Delete
End Sub
 
Back
Top