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 row hiding based on conditions

Discussion in 'Business Applications' started by whschimmel, Dec 6, 2011.

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

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Hello There,

    I'm trying to do the following. I've got an Excel File (With one sheet). In this file some transport costs are specified.
    The first two rows of this cost sheet are the weight and size

    I want to hide one of these first two lines so it does not get calculated in formulas in the rows below. How should this be hidden;

    If the weight is bigger than the size, the weight should be calculated in the total amount below
    but, If the size is bigger than the weight, the size should be calculated in the total amount below

    So the highest amount of the first two lines should be used to calculate the total amount below, Also I would like to hide the row with the lowest amount, so people understand the calculation of the total amount

    I tried conditional formatting with no success. What is the easiest way to get this done? By Macro or VBA project?

    Any help would be appreciated.

    thanks,
    Wouter
     
  2. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    FYI:

    The two cels with the amounts (of the first two rows) are:
    E9 + E10

    Deleting the lowest of the first two rows is also an option...
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    It all depend upon your final formula, you could hide the entire row based upon the lowest value.
    If you select the entire row and with conditional format define it only for that cell (e9 or E10) you then hide it in the selnse that you could maken the font white on a white background, it's not visible but the value is still there
     
  4. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Hi There,

    I've added the excel file, so it's a bit easier to see what I want to do.

    The two red lines are the two lines in question;

    • When the tonnage (per ton, line 9) is higher then the dimensions (per cbm, line 10), the tonnage should be shown and the dimension line should be hidden

    • When the dimensions (per cbm, line 10) are higher then the tonnage (per ton, line 9), the dimensions should be shown and the tonnage line should be hidden
     

    Attached Files:

  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Git it, will try and take a look sometime this weekend :)
     
  6. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Great I appreciate your help!

    Have a great weekend,
    Wouter
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Wouter

    Try the code below
    Copy it and paste it in the VBA project of Sheet2
    It hides the row adn also changes the formula, I don't know if that was necessary but it seems to me that you don't add the hidden row or is that an incorrect assumption?

    If so just paste your formula back after you remove the two formula rows in the code (red)

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("E9").Value > Range("E10").Value Then
        Range("E9").EntireRow.Hidden = False
        Range("E10").EntireRow.Hidden = True
        [COLOR="red"]Range("E16").Formula = "=R[-7]C+SUM(R[-5]C:R[-2]C)"[/COLOR]
    ElseIf Range("E9").Value < Range("E10").Value Then
        Range("E9").EntireRow.Hidden = True
        Range("E10").EntireRow.Hidden = False
        [COLOR="Red"]Range("E16").Formula = "=R[-6]C+SUM(R[-5]C:R[-2]C)"[/COLOR]
    End If
    End Sub
    
     
  8. whschimmel

    whschimmel Thread Starter

    Joined:
    Jul 7, 2008
    Messages:
    168
    Got it working now,

    placed the code in the Personal Macro Workbook and added a shortcut to the module. Now the code can be run every time when the shortcut is used!

    Thanks!
    Wouter
     
  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/1029927

  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