# 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

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

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

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

• 21 KB Views: 59

#### Keebellah

Hans
Git it, will try and take a look sometime this weekend #### whschimmel

Git it, will try and take a look sometime this weekend Have a great weekend,
Wouter

#### Keebellah

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

#### whschimmel

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.