Hiding unneeded rows through a formula?

bdgray

New Member
Joined
Feb 19, 2002
Messages
5
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?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top