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

[Excelator]

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!

 

[alansidman]

Here is a VBA solution for you.

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]

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]

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]

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.