Auto hiding rows

[The Grinch]

Hello,

I have a large list of three columns of data which is constantly being added to. I want to display the list such that only the last ten rows of data are visible. If the list were static I could easily manually hide the desired rows. However, the list is dynamic.

How can set the list to hide rows automatically in order to only show the last ten rows?

Note: I can't do this through auto filtering because I would have to re-apply the filter every time the list was updated.

Any thoughts suggestions appreciated.

CHEERS

[Rutgers_Excels]

Are you familiar with writing code in VBA? You can write a simple code which you can place in that worksheet that will accomplish this task for you.

[The Grinch]

Thank you.

Could you post some sample code?

[Rutgers_Excels]

You need to be a little more specific as to which rows have data etc. I'm having a little difficulty visualizing what exactly it is that you are looking for.

[The Grinch]

Thanks again for your reply.

Column E has the numbers 1,2,3.......2000. and is growing, ie tomorrow someone might add 2001, 2002, 2003.

I want all rows with a number less than max(row E)-10 in their E column to be automatically hidden.

CHEERS

[Rutgers_Excels]

You need to put this code in whichever workbook you are working in.


Private Sub Worksheet_Change(ByVal Target As Range)
'Sets Activecell
aCell = ActiveCell.Row
bCell = ActiveCell.Column

'Hides rows if there are more than 10 visable
Range("E1").Select
Selection.End(xlDown).Select
r = Selection.Row
If r < 10 Then
End
End If
Range(Cells(1, 5), Cells(r - 10, 5)).Select
Selection.EntireRow.Hidden = True

'Selects Original Activecell
Cells(aCell, bCell).Select
End Sub


This will only show the last 10 rows of data. This also assumes that you have 2 or more lines of data already. If you have no data on the sheet or only one row of data, this WILL NOT WORK.

Let me know if this is what you were looking for.

[The Grinch]

Thanks

I open the spreadsheet and went to the tab, then right clicked on the tab name and clicked view code, then pasted your code.

nothing has happened have i put it in the right place?

[Rutgers_Excels]

Did you try to change anything on your sheet yet? It will only update after you change something on your sheet. Also, is all of the information on that sheet linked to another sheet? If so, the code needs to be modified. Remember that the code is sourcing column E. So it will only start hiding rows once new data is entered into column E.