Solved: Excel row hiding based on conditions

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

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...
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

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
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Git it, will try and take a look sometime this weekend :)
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
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
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top