Solved: Trimming Things Down...

[compariniaa]

I have over 2,000 entries in one spreadsheet, with revenue figures in column
H, and the figures are sorted ascending
To provide the user with a quick overview on a printout, I wanted to
"compress" everything so it would fit onto one landscape-oriented page. By
compress I mean hide the middle values, not zoom out. By hiding the middle
values, only the biggest losers and the biggest gainers are shown.
How could I write a sub that kept hiding the middle rows until only 50 rows are visible?

[mvidas]

Hello,

Take a look at the following, should be what you need:[vba]Sub compariniaa()
With ActiveSheet.UsedRange
If .Rows.Count > 50 Then
.Range(.Rows(26), .Rows(.Rows.Count - 25)).EntireRow.Hidden = True
End If
End With
End Sub[/vba]Please let me know if its not what you're looking for!
Matt

[mdmackillop]

This should show the top and bottom 25 values, whether the data is sorted or not.
Regards
MD
[VBA]Sub Ranks()
Dim MyRange As Range
Set MyRange = Intersect(Columns(8), ActiveSheet.UsedRange)
Application.ScreenUpdating = False
For Each cel In MyRange
If Application.WorksheetFunction.Rank(cel, MyRange) < MyRange.Cells.Count - 25 And _
Application.WorksheetFunction.Rank(cel, MyRange) > 25 Then
cel.RowHeight = 0
End If
Next
Application.ScreenUpdating = True
End Sub
[/VBA]

[compariniaa]

Matt and MD, that's excellent! That's exactly what I needed, thank you very much for your help