Hiding unneeded rows through a formula?

[bdgray]

I have created a pricing template that I use to price services customers on a per location basis. Each row from 10 to 60 corresponds to a location on this spreadsheet. Currently I have 50 rows which can handle up to 50 locations. The problem is when the customer has say only 20 locations, I am left with 30 blank rows.

I can hide the rows manually but I would like to be able to hide unused rows through a formula. Example, have cell a5 ask "How many locations" and cell b5 be the input. So if I enter 20 in that cell, the bottom 30 rows in my spreadsheet would hide.

Is this possible?

[Mark W.]

Excel formulas don't perform actions such as formatting, hiding rows, etc. They only return values. Consider using Excel's Data | Filter | AutoFilter menu command.

[ This Message was edited by: Mark W. on 2002-02-26 10:00 ]

Some days ago, I asked the same question to Mr. Excel Message Board. Try with a macro.May be something like this:

Unused=Cells(2,5).value 'value in b5

Range("A" & Unused & ":G50").Select

Set Rng = Selection

For R = Rng.Rows.Count To 1 Step -1

If Rng.Cells(R, 1).Value = 0 Then
Rng.Rows(R).EntireRow.Hidden = True
Else
If Rng.Cells(R, 1).Value = 1 Then
Rng.Rows(R).EntireRow.Hidden = False
End If
End If
Next R

In this case, besides checking that the row is in the range of unused rows, you are asking for the value in column A of the row: if it is zero, you hide the row; if it is 1, you unhide the row.

HTH.

Caliche

[gplhl]

On 2002-02-26 09:36, bdgray wrote:
I have created a pricing template that I use to price services customers on a per location basis. Each row from 10 to 60 corresponds to a location on this spreadsheet. Currently I have 50 rows which can handle up to 50 locations. The problem is when the customer has say only 20 locations, I am left with 30 blank rows.

I can hide the rows manually but I would like to be able to hide unused rows through a formula. Example, have cell a5 ask "How many locations" and cell b5 be the input. So if I enter 20 in that cell, the bottom 30 rows in my spreadsheet would hide.

Is this possible?

Another way could be:

Sub HideRows()
'Unhides the whole of the sheet first
Cells.Select
Selection.EntireRow.Hidden = False
'Hides sheet from row value of B5 + 1 to row 50
Rows(Range("B5").Value + 1 & ":50").Select
Selection.EntireRow.Hidden = True
End Sub

Just change 50 with the number of the last row that you would want hidden and change + 1 so that the value you enter in B5 adds up to the row number you want hidden.

Regards,

Gary Hewitt-Long

[Autolycus]

On 2002-02-26 09:36, bdgray wrote:
I have created a pricing template that I use to price services customers on a per location basis. Each row from 10 to 60 corresponds to a location on this spreadsheet. Currently I have 50 rows which can handle up to 50 locations. The problem is when the customer has say only 20 locations, I am left with 30 blank rows.

I can hide the rows manually but I would like to be able to hide unused rows through a formula. Example, have cell a5 ask "How many locations" and cell b5 be the input. So if I enter 20 in that cell, the bottom 30 rows in my spreadsheet would hide.

Is this possible?

If you can hide based on the cells in a paticular column being blank (or a particular range within a paticular column) then you could use this one line macro (which is based on column A) :-

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

The macro recorder could be used to create code to do the same thing.