How Can I hide unused Rows in Spreadsheets

[PeterRyan]

I have a financial forecasting system built in Excel which runs to 30
pages. It is designed for businesses and includes budget, cash flow
and forecast balance sheets and is able to be updated monthly with
actual results which then drive the projections for the rest of the
year. So that it can be used by a wide range of businesses, it has a
large number of rows on each sheet – for instance it has 16 rows for
different sales types and has sufficient rows for over 110 overhead
expenses. This is because the expenses are broken down into groups – eg
Selling Expenses, Financial, Admin etc.


The reports that are produced have to have the rows included so that
they can be linked – for instance the profit and loss reports need to
have the 110 rows available even though they are never all used by one
company. So it is necessary to manually hide the rows with nothing in
them to avoid having reports with great expanses of white. This is time
consuming and also a bit dangerous where a row is hidden and then
subsequently used – it is easy to miss unhiding it.



How can I easily hide/unhide rows with nothing in them?

 

[Don Guillett]

have you tried data>filter>autofilter

 

[Greg Glynn]

Unfortunately, the "AutoFilters" will only work with Columns

This should do it for you Sir

Sub HideBlankRows(
Dim rng As Range, cell As Rang
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A:A")
For Each cell In rn
If Application.CountA(cell.EntireRow) = 0 Then cell.EntireRow.Hidden = Tru
Nex
End Su

Change the RANGE("A:A") to something smaller if you don't want the whole whole sheet processed. Perhaps give your 110 rows a Name like "ForecastData" and use RANGE("ForecastData") in the Macro.

 

[Anders S]

Another way is to select the column where there are no entries (or part of it),
then Edit>Goto>Special, choose Blanks + OK. Then Format>Row>Hide.
To reveal all rows, select the whole sheet (click above/left of cell A1), then
Format>Row>Unhide.

HTH
Anders Silven

have you tried data>filter>autofilter