# Hide Row based on the value in a Cell of that Row

#### Excelator

##### New Member
Greetings!

Happy to find you guys after a bit of head banging. I've Googled up a dozen different ways to almost do what I'm trying to do, but just couldn't get anything to work. Anyway...

It all started with a curiosity about what taxes would look like if the RATE and the BRACKET increments were actually equal. I used names for RATE and BRACKET values and everything works fine, but even in a theoretical exercise you can't have a tax rate more than 100%, so I thought I'd just automatically hide any row that showed a RATE of more than MAX_RATE or greater.

Not so easy - at least not for me.

Also, as an afterthought, I added a dropbox for a YES/NO option to TAX_THE_POOR, which logically would render that rate (located in cell J8) a zero, but currently does nothing.

I would really appreciate pointers from anyone that has a grasp on this kind of thing.

File: https://1drv.ms/x/s!ApUGVE4pNzgMiggyue1PhV0SlOhL

Thanks!

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### alansidman

##### Well-known Member
Here is a VBA solution for you.

Code:
``````Option Explicit

Sub HideRows()
Dim lr As Long, i As Long
Dim MaxR As Range
lr = Range("J" & Rows.Count).End(xlUp).Row
Set MaxR = Range("D7")
For i = lr To 8 Step -1
If Range("J" & i) > MaxR.Value Then
Range("J" & i).EntireRow.Hidden = True
End If
Next i
End Sub``````

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

#### Excelator

##### New Member
That's awesome! Thank you!
Is there a way to make that trigger when either RATE or BRACKET is changed? (Really I guess it could be triggered by any change.)
Either way, thanks again!

#### Excelator

##### New Member
Also, I see it only works the first time. After that the rows that were hidden cannot be evaluated until I select the entire page and use the unhide feature. Not sure what can be done about that. I guess the goal would be to somehow automatically unhide all rows and reapply the macro every time RATE or BRACKET is changed, so then it would show a full tax rate schedule (all the way up to MAX_RATE).

#### alansidman

##### Well-known Member
Would it be acceptable that no rows are hidden when the file is opened? Then apply a macro to hide any rows that meet the requirements. I am confused however, if you have a row with a rate greater than the max that was previously hidden but once you open the file, it is unhidden, but now you want to trigger a row action if the bracket or rate change. If we go with a change action then the previously hidden will not be hidden.

Replies
5
Views
193
Replies
0
Views
324
Replies
5
Views
19K
Replies
4
Views
592
Replies
3
Views
250

1,195,590
Messages
6,010,612
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back