Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Can you conditionally hide a cell?

  1. #1
    Guest

    Default

    I want to use an IF command to hide a cell if it has no value as a way of consolidating my list. I've tried conditional formating but can't find a "hide" selection. Can it be written in code? Any suggestions would be appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can't "Hide" a cell with conditional formatting, but you could change the font color to your sheet color if you don't want to see it.

  3. #3
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Location
    Flanders
    Posts
    484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, almost everything is possible...
    if you use vba

    Use this code and if the entire row is empty, it will be hidden.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    If Target = "" Then
        Rows(Target.Row).Select
        If Application.CountA(Selection) = 0 Then
        Selection.EntireRow.Hidden = True
        End If
    End If
    
    End Sub

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    denver
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Bruno, but the rows that I want hidden have "0" values in them. Your code is not working to hide them. I still get something like this:

    Market Analysis
    0
    Advertising
    0
    0
    Sales
    Etc.

    If you can help me with this I would appreciate it very much.

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

    Default

    Perhaps my probelem with your code lies in the covnersion. I keep getting an error with this line:

    If Target = "A1:A125" Then

    I'm pretty sure it's because of the target values but not sure how to correct. Please advise.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This should read

    If Target.Address = "$A$1:$A$125" then
    Regards,

    Juan Pablo González
    http://www.juanpg.com

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

    Default

    Thank you for your help, but the rows are still present after intering the VBA code.
    Any suggestions?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    just a thought - have you checked that all the cells in the rows are actually empty?
    another thought - not sure if this would affect anything, but do you have any merged rows or columns in the target range?

    [ This Message was edited by: anno on 2002-02-20 19:27 ]

  9. #9
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Location
    Flanders
    Posts
    484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There was something wrong with the previous posting...
    (Juan, you may delete it ?)

    On 2002-02-20 11:25, sho-nuff wrote:
    Thank you Bruno, but the rows that I want hidden have "0" values in them. Your code is not working to hide them. I still get something like this:

    Market Analysis
    0
    Advertising
    0
    0
    Sales
    Etc.

    If you can help me with this I would appreciate it very much.
    Try something like this if you want to hide the cells with "0" and the empty cells and if you only want to check the changed cell :

    This code doesn't check other columns.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    If Target = "" Then
        Rows(Target.Row).EntireRow.Hidden = True
    ElseIf IsNumeric(Target) Then
        If Target.Value = 0 Then
            Rows(Target.Row).EntireRow.Hidden = True
        End If
    End If
    
    End Sub
    [ This Message was edited by: Bruno on 2002-02-21 02:22 ]

  10. #10
    New Member
    Join Date
    Feb 2002
    Location
    denver
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    now I'm getting a script error with my target line:

    If Target = "$A$1:$A$125" Then

    Need I broaden the range or narrow it?

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
  •