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

 

[Roli001]

Does this solve your problem?

Sub deleteRow()

With Sheets("Sheet2")

    Do Until IsEmpty(.Cells(101, 1))
         .Rows(1).Delete Shift:=xlUp
    Loop

End With

End Sub

 

[genepaton]

That works fantastically.
I thought it would be a simple script, but couldn't get my head around it.
Any reason why you need to offset the column by 1 but?

 

[Roli001]

That works fantastically.
I thought it would be a simple script, but couldn't get my head around it.
Any reason why you need to offset the column by 1 but?

Good to here.

I'm assuming you are asking about 'Shift:=xlUp'. This actually moves all the rows below the deleted row into it's position avoiding empty rows.

 

[mikerickson]

This doesn't loop

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

 

[genepaton]

Hi Roli,

I was actually referring to the Do Until IsEmpty(.Cells(101, 1))

With my limited knowledge, that means row 101 offset 1 (column B) doesn't it?

Hi Mikerickson,

Thanks for your input.
What would the benefit of looping or non-looping be?

 

[mikerickson]

Looping (particularly when changing screen elements) is slower than not.

 

[genepaton]

There seems to be another glitch now; i have named these columns of data that we're adding to a removing from the top, but every time it deletes the top row the 'names' lose their reference. Therefore, the charts that i linked via the names are bombing out.

Any suggestions?

 

[mikerickson]

What are the name definitions?

 

[genepaton]

5 columns, A to E all with headings in the first row.
(I had to modify the scripts you suggested to delete the second row instead of the first)
The named ranges are then written as OFFSETS;
eg. Name AWT"=OFFSET(Sheet3!$B$1,0,0,COUNTA(Sheet3!$B:$B)-1)"
for column B.

And so on for columns C, D and E (Column A is just a time stamp of when the data was copied over)

I then reference these names with the chart.

It seems because the scripts are deleting row 2, its then stuffing up the 'names' with an invalid reference.

I have worked around it in a very messy way, by naming the ranges from B1 down, and then adjusting the x axis on the chart to start at 3. Which seems to be working, but just doesn't seem "tidy" to me.