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: Excel 2007 Hide Row vba

Discussion in 'Business Applications' started by Technodruid, Jan 6, 2011.

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

    Technodruid Thread Starter

    Joined:
    Sep 8, 2010
    Messages:
    72
    Hey, I need to Hide Row's 34-47 Based on a summed Value in Y34 -Y47. IE if Y34 = 0 then Row 34 is hidden, if Y35 = 0 then 35 is hidden Ive tried this with a couple different code and it keeps erroring. Error 425
    Code:
    Private Sub Worksheet_Calculate(ByVal Target As Range)
    If Target.Address = "$Y$34" Then
    If IsNumeric(Target) Then
    Rows(34).EntireRow.Hidden = (Target.Value = 0)
    End If
    End If
    End Sub
    
    Does Any one Have a simple way to Hide the rows? They will always be 34-47 and Based on Y34 -Y47.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Maybe this?

    Private Sub Worksheet_Calculate()
    For Each Cell In Range("Y34:Y47")
    If Cell.Value = 0 Then
    Rows(Cell.Row).Hidden = True
    Else
    Rows(Cell.Row).Hidden = False
    End If
    Next Cell
    End Sub

    (BUT: if the (e.g.) Y34 summed value references another cell in row 34, how will you edit it once it's hidden?)
     
  3. Technodruid

    Technodruid Thread Starter

    Joined:
    Sep 8, 2010
    Messages:
    72
    Thanks Bomb, Code worked almost perfect. I had to turn off auto calculate and put calculate in to the workbook code below my refresh otherwise it tried to run forever.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Nice catch, thanks. (y)
     
  5. Technodruid

    Technodruid Thread Starter

    Joined:
    Sep 8, 2010
    Messages:
    72
    Okay, I need to find a fix for this that doesnt require me to remove the Auto Calc feature. So had to unsolve it.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try disabling events like I've shown in the code below.

    Code:
    Private Sub Worksheet_Calculate()
    
    [B]Application.EnableEvents = False[/B]
    For Each Cell In Range("Y34:Y47")
    If Cell.Value = 0 Then
    Rows(Cell.Row).Hidden = True
    Else
    Rows(Cell.Row).Hidden = False
    End If
    Next Cell
    [B]Application.EnableEvents = True[/B]
    End Sub
    Rollin
     
  7. 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/973025

  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