Macro to Hide/Unhide Rows Based on Results in Column Equation

[jwhite0720]

Hi there! I'm new to using these forums, but have spent some time scouring them from time to time to look for simple solutions to any excel/VBA problems I'm having. I consider myself relatively intermediate to advanced in Excel as far as formulas and spreadsheet creation goes, however VBA is foreign to me, and an area I'd love to learn. If any of you have recommendations on a good resource to begin learning VBA, please send it my way. And now....onto my macro question.

I've got a workbook with many tabs, and one or two input tabs feed information into another summary tab using various lookup and index formulas. There are any where from 40 to 80 rows to be qualified depending on the specific summary sheet (therefore I will likely need the macro to reference only the active sheet as it needs to work the same way on different sheets independent of each other). What I would like to do is if the result of the formula in column A of the summary sheet is "----------" (starts at A7) then I would like to hide that row, and if the data is changed on the input tab to result in anything other than the qualifying "----------", for that row to be unhidden. If this can be done dynamically that'd be great, however, if a button needs to be put into the spreadsheet to execute the macro, that will be fine too.

Thanks so much in advance for your help, and I promise I'll do my best to begin learning VBA so I can help contribute in return!

[mojo249]

Put this code behind the summary sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K1")) Is Nothing Then
    Set Rng = Range("A7:A" & Range("A50000").End(xlUp).Row & "")
    
    For Each x In Rng.Cells
        If x.Value = "----------" Then
            Rows(x.Row).Hidden = True
        Else
            Rows(x.Row).Hidden = False
        End If
    Next x
End If

End Sub

The If Not Intersect line is optional but it means the code will only run if a cell (or cells) within a defined range is changed (in this case cell K1). Otherwise it will run if any cell on the sheet is changed.

[Charles]

This user cross post the same question.


http://forums.techguy.org/business-a...ml#post8143487

and

http://www.mrexcel.com/forum/showthr...de+unhide+rows

[mojo249]

It really annoys me when people spend time helping somebody and 1) They've asked the same question on different forums (probably wasting their time too); and 2) They don't even bother to reply with a simple thank you.