+ Reply to Thread
Results 1 to 11 of 11

(SOLVED) Hiding rows based on a cell value that comes from a formula

  1. 03-26-2014, 06:27 AM #1
    Dbouwheer
    Dbouwheer is offline
    Registered User
    Join Date
    03-25-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Thumbs up (SOLVED) Hiding rows based on a cell value that comes from a formula

    Dear all,

    I am quite new to VBA but i am a fast learner and hope some of you can help me with the following:
    I have searched dozens of forums and theads, but i cant seem to find an answer.

    In my file I am trying to hide Rows (33:41) based on a cell [G4] value (HIDE,UNHIDE) or (1,2) doesn't really matter which one.

    Now i have used the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("I4").Value = "HIDE" Then
    Rows("33:41").EntireRow.Hidden = True
    ElseIf Range("I4").Value = "UNHIDE" Then
    Rows("33:41").EntireRow.Hidden = False

    End If
    End Sub¸

    The problem that I have is that it works as long as I manually type in the value, but this value is generated by a formula therefor nothing happens.

    Hope you guys understand what i am asking

    Thanks in advance !
    Last edited by Dbouwheer; 03-26-2014 at 08:15 AM. Reason: SOLVED
    Reply With QuoteRegister To Reply
  2. 03-26-2014, 06:39 AM #2
    Leon V (AW)
    Leon V (AW) is offline
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    365
    Posts
    330

    Re: Hiding rows based on a cell value that comes from a formula

    try swapping .value for .text

    also it will be case sensitive is your formula outputting UNHIDE or Unhide?
    Last edited by Leon V (AW); 03-26-2014 at 06:42 AM. 
    Sub Reputation()
Dim Problem as Variant
Dim Reputation as Integer
For Each Problem in Forum.Threads
    If Problem.Title = "*[Solved]*" and Solver.Name = "Leon V (AW)" Then Reputation = Reputation + 1
Next Problem
End Sub
    Reply With QuoteRegister To Reply
  3. 03-26-2014, 06:46 AM #3
    Dbouwheer
    Dbouwheer is offline
    Registered User
    Join Date
    03-25-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Hiding rows based on a cell value that comes from a formula

    Hello Leon, thank you for your effort

    I have tried it, however the same problem occurs..
    It does not seem to recognize the change If it comes from a formula.

    Djonno
    Reply With QuoteRegister To Reply
  4. 03-26-2014, 06:53 AM #4
    Leon V (AW)
    Leon V (AW) is offline
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    365
    Posts
    330

    Re: Hiding rows based on a cell value that comes from a formula

    what is the formula?
    i was testing it with =1+1 but im guessing yours has a more complex output.
    Reply With QuoteRegister To Reply
  5. 03-26-2014, 06:56 AM #5
    Dbouwheer
    Dbouwheer is offline
    Registered User
    Join Date
    03-25-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Hiding rows based on a cell value that comes from a formula

    The formula I am using=

    IF(G4=1;"HIDE";"UNHIDE")

    (The G4 value comes from a combobox that is directly in the worksheet)
    Reply With QuoteRegister To Reply
  6. 03-26-2014, 07:24 AM #6
    Leon V (AW)
    Leon V (AW) is offline
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    365
    Posts
    330

    Re: Hiding rows based on a cell value that comes from a formula

    Ah if its in a combo box it wont trigger the worksheet_change event.
    it needs to be in a module sub triggered by the dropdown box. 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 03-26-2014, 07:52 AM #7
    Dbouwheer
    Dbouwheer is offline
    Registered User
    Join Date
    03-25-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Hiding rows based on a cell value that comes from a formula

    Leon thank you for your help so far !

    I am trying to do this, but somehow I think i am doing something wrong?

    I have attached an example file of what the original setup looks like.

    Could you maybe insert the code to make it hide rows based on the value of [I4} ?

    Much appreciated !
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-26-2014, 08:04 AM #8
    Leon V (AW)
    Leon V (AW) is offline
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    365
    Posts
    330

    Re: Hiding rows based on a cell value that comes from a formula

    can't open attachments (secure network)
    right click on the dropdownbox and select "Asign macro" and choose "New" pste the code into that sub and it should work fine.
    Reply With QuoteRegister To Reply
  9. 03-26-2014, 08:09 AM #9
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hiding rows based on a cell value that comes from a formula 

    Please Login or Register  to view this content.
    Place this in the sheet module behind Blad 1 (right click the tab--> view code --> Paste code).
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."
    Reply With QuoteRegister To Reply
  10. 03-26-2014, 08:14 AM #10
    Dbouwheer
    Dbouwheer is offline
    Registered User
    Join Date
    03-25-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Hiding rows based on a cell value that comes from a formula

    Thank you both so much! Both solutions worked !
    Reply With QuoteRegister To Reply
  11. 03-26-2014, 08:15 AM #11
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: (SOLVED) Hiding rows based on a cell value that comes from a formula

    Thanks for the Rep! If you are happy with the solution please mark the thread as [SOLVED] using the thread tools at the top.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hiding rows based on cell value.
    By Metalgijs in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-12-2013, 03:08 AM
  2. Hiding rows in one tab based on cell value in another
    By hoffmt28 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2013, 09:27 AM
  3. Hiding Rows based on Formula result
    By Viscount_Grey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-21-2011, 08:59 AM
  4. Hiding rows based on value in another cell
    By sahar in forum Excel General
    Replies: 0
    Last Post: 10-12-2011, 10:27 AM
  5. Hiding Rows Based based upon cell value being blank
    By leelee23 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2007, 04:48 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1