PDA

View Full Version : Solved: Trimming Things Down...



compariniaa
06-26-2006, 10:44 AM
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
06-26-2006, 11:07 AM
Hello,

Take a look at the following, should be what you need: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 SubPlease let me know if its not what you're looking for!
Matt

mdmackillop
06-26-2006, 11:17 AM
This should show the top and bottom 25 values, whether the data is sorted or not.
Regards
MD
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

compariniaa
06-26-2006, 11:18 AM
Matt and MD, that's excellent! That's exactly what I needed, thank you very much for your help