1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Hide Columns based on value of cell

Discussion in 'Business Applications' started by alsupport, Aug 31, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. alsupport

    alsupport Thread Starter

    Joined:
    Aug 18, 2010
    Messages:
    7
    Hi guys,

    Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

    i.e

    if C24 is 0, then hide column C
    if D24 is 0, then hide column D
    etc...


    Sub Hide_Column ()

    If Range("H24").Value = 0 Then
    Columns("H").EntireColumn.Hidden = True
    Else
    Columns("H").EntireColumn.Hidden = False
    End If

    End Sub
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I think this may work (I am a pretty poor coder....) :rolleyes:
    Code:
    Dim x As Integer
    x = 2
    For i = 1 To 24
        chk_value = Cells(24, x + i)
        
        If chk_value = 0 Then
        
            Columns(x + i).EntireColumn.Hidden = True
        
        End If
    Next i
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    One thing - if a cell is empty (a null) it also gets hidden.
    Oh, and start the code with something like
    Sub HideColumns()
    and end it with
    End Sub
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Sub M2()
    Range("C:Z").EntireColumn.Hidden = False
    Rows(1).Insert
    Range("C1:Z1").FormulaR1C1 = "=IF(AND(R[24]C=0,R[24]C<>""""),#N/A)"
    Range("C1:Z1").SpecialCells(xlCellTypeFormulas, 16).EntireColumn.Hidden = True
    Rows(1).Delete
    End Sub
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    bomb, old man, I know you are the far better coder, so what are the advantages of your code over mine (which I grabbed from somewhere else, but I don't know who wrote it or I would give credit for the work)??
    Does it get around the problem with null cells?
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Yep, I posted to address the null cells issue*. Run:

    Range("C1:Z1").FormulaR1C1 = "=IF(AND(R[24]C=0,R[24]C<>""""),#N/A)"

    by itself to get a formula with a "double-check" -- equals 0 and doesn't = "".

    (*plus there's no loop :))
     
  7. alsupport

    alsupport Thread Starter

    Joined:
    Aug 18, 2010
    Messages:
    7
    thanks guys!
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Duh - I should have read the IF() more carefully. And no loop - much better!! (y) (y)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/947039

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice