A Macro That can Hide Unnecessary Rows

[oddity]

Hello guys, I need some help with a function / macro. I prepared an excel file that lists loads of materials we use for our production. I can manually enter the amount of products to be manufactured and it shows me separated and accumulated costs, prices, materials we immediately need to buy from suppliers etc. Here is a view from the worksheet:

Here is what I have to achieve: When I click on a cell, for example BP6 for "EXIT-AD1", I want the macro to automatically HIDE all unnecessary rows, thus showing me only the populated cells under EXIT-AD1.
So, let's say when I hit BP6 cell, macro will automatically HIDE every row that has an empty cell on the BP column like 12th, 14th, 18th, 19th, etc. BTW, rows from 7th to 292nd are partially populated. As soon as I hit another cell on the 6th row, macro will have to perform the same action for that particular column. I also need a "Reset" button to unhide all rows in that range. I definetly hope it is possible, otherwise I will have to prepare about 100 pages seperately to demonstrate a single products components & cost. Please help me, and help me soon enough

 

[oddity]

I found this topic lurking through the forum: http://www.mrexcel.com/forum/excel-questions/568738-auto-hide-rows-if-any-cells-column-g-0-a.html

It is kind of a dumb job to copy and paste this code all over; assigning seperate buttons for each and every product and rearrange the code for the particular column letters. Still, I could do it, no matter what. Bu I simply can't because the code doesn't reset the process before hiding the empty rows for other columns.

So when I assign a button to use the macro for hiding rows, let's say BP6, it obviously hides 32nd row (see OP snapshot) which is actually populated on let's say BG column. Now, if I rearrange the code to be able to work for BG column, after hitting the new button I still can't see the 32nd row and it definetly doesn't work for me. I need a more easy-to-use code or at least a "reset" code (hey, I could go for it as is even the "undo" button of Excel worked but it stays N/A after hitting the assigned buttons).

BTW, I need the buttons to be sticked to the involved cells so they won't be able to go away while changing column widths. I'm sorry if my explanations are kind of amateurish

 

[JLGWhiz]

Copy this code to the worksheet code module for the sheet where you want to hide the rows. How it works: Once you install the code, if you click any where on row 6, and only row 6, all rows from row 7 down with a bland in the column you click on will be hidden. To Unhide the rows when you are finished, click in the cell above the 1 and left of "A" , row and column designators, right click, then click "Unhide" in the pop-up menu. Or you can install the second code below in your standard code module and assign it to a keyboard shortcut.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Rows(6)) Is Nothing Then
    lr = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    Range(Cells(7, Target.Column), Cells(lr, Target.Column)).SpecialCells(xlCellTypeBlanks) _
    .EntireRow.Hidden = True
End If
End Sub

this code would unhide the rows on the active sheet.

Sub unhiderows()
ActiveSheet.Rows.Hidden = False
End Sub

 

[oddity]

Copy this code to the worksheet code module for the sheet where you want to hide the rows. How it works: Once you install the code, if you click any where on row 6, and only row 6, all rows from row 7 down with a bland in the column you click on will be hidden. To Unhide the rows when you are finished, click in the cell above the 1 and left of "A" , row and column designators, right click, then click "Unhide" in the pop-up menu. Or you can install the second code below in your standard code module and assign it to a keyboard shortcut.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Rows(6)) Is Nothing Then
    lr = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    Range(Cells(7, Target.Column), Cells(lr, Target.Column)).SpecialCells(xlCellTypeBlanks) _
    .EntireRow.Hidden = True
End If
End Sub

this code would unhide the rows on the active sheet.

Sub unhiderows()
ActiveSheet.Rows.Hidden = False
End Sub

Hey, thanks for the response! Unfortunately I couldn't manage to make it functional. I copied the first code to the code module but nothing happened afterwards. I mean I clicked everywhere on the 6th row but nothing changed. Btw I need it to be operational on the 6th row (that's true) but only from H column to FO column.

The 2nd code is working though, I now have a reset button at least On the other hand, it has a downside for me. It unhides everything! I have some hidden formula rows underneath the main table and I don't want them to be visible without my permission (sheet will be password protected). How can I change the "reset" code to work in a certain range of rows (for unhiding all rows only from 7th to 292nd)?

Forgive me if I want too much but I'm fairly new on Excel and I'm pretty confused about this stuff

 

[JLGWhiz]

Did you copy it to the Worksheet code module or the standard code module? To access the worksheet code module, right click the sheet name tab, then click "View Code" in the pop up menu. Three reasons it would not have run before, more than one cell selected, still in design mode or wrong code module. If you elininate those three possibilities, the code should run as expected. It did run as expected when tested.

 

[oddity]

OK, I managed to make it work, thanks to your code module help. I also fixed my problem with the unhiding (resetting) range like this:

Sub Unhide()
Rows("7:289").EntireRow.Hidden = False
End Sub

But I couldn't fix the row range issue I'm having. You see, my co-workers will use this table so I don't want any unnecessary features. I only want the code to work if the users click on cells on the 6th row but only in the H-FO columns range. I'd really appreciate if you help with that too.

 

[JLGWhiz]

This will now only execute if the user clicks in row 6 for H:FO.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H6:FO6")) Is Nothing Then
    lr = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    Range(Cells(7, Target.Column), Cells(lr, Target.Column)).SpecialCells(xlCellTypeBlanks) _
    .EntireRow.Hidden = True
End If
End Sub

 

[oddity]

That is purely brilliant, thank you very much for your help!

 

[JLGWhiz]

You're Welcome,
Regards, JLG