Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Hiding unneeded rows through a formula?

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Guest

    Default

    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



  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •