Macro to limit how many rows of data, and delete from the top

[genepaton]

Hi All,

I've been trying to figure out a simple macro that will count how many rows on a worksheet, and delete from the top anything that exceeds the desired value (shifting the other rows up).

Im copying rows of data over to the sheet by another macro, but say i wanted it to limit it to 100 rows of data, i would like it so when ever i run the macro that copies the data, if the 2nd sheet with the data exceeds this 100 row limit, anything in row 1 on that sheet will be deleted and all the rows shifted up (the older data is always at the top of the list).

Any help will be much appreciated

 

[mikerickson]

What's happening is that $B$1 is going away, causing the REF error.

Try this formula
=INDEX(Sheet3!$B:$B, 1, 1):INDEX(Sheet3!$B:$B, COUNTA(Sheet3!$B:$B)-1, 1)

(Is the -1 in your post a typo?)

 

[genepaton]

No, the -1 wasn't a typo, but the first reference to the cells were;
=OFFSET(Sheet3!$B$2,0,0,COUNTA(Sheet3!$B:$B)-1) is what i had it as before doing it the messy way.

But i guess your point would still apply for that, as the macro im running deletes row 2.

I tried yours, but its missing the last row of data.
Its capturing from the heading down to say row 99 of 100.

 

[mikerickson]

This code will leave the last 100 rows and the headers in row1

Sub test2()
    Dim keepRowCount As Long
    On Error GoTo ErrorOut
    
    keepRowCount = 100
    
    With Sheet1.Range("A:A")
        With Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
            .Resize(.Rows.Count - keepRowCount, 1).EntireRow.Delete shift:=xlUp
        End With
    End With
    
ErrorOut:
    On Error GoTo 0
End Sub

Also, if the dynamic range is to miss the header row, this would be the formulation.

=INDEX(Sheet3!$B:$B, 2, 1):INDEX(Sheet3!$B:$B, COUNTA(Sheet3!$B:$B)-1, 1)

 

[genepaton]

Hi Mikerickson,

Hope you had a good weekend!
I've tried out your scripts this morning, the macro still doesn't seem to be working but the formula for the name definitions is great (i just had to remove the -1 at the end of, as this was cutting off the last row of data again).

Im not too worried about the macro, as Roli's is working well even if it does loop. There's no perceivable lag. If you're interested, it's not limiting the rows. I had added your scripting into the other macro im using to copy the data over (incl the 'Dim keepRowCount As Long' at the beginning of my macro) but it just keeps adding rows of data and not removing the 2nd row (oldest row of data beneath headers).

Thanks for your help with the name definitions!