Hiding rows base on the value of up 5 cells

[craig0357]

Hi My knowledge of Excel is limited, but i wanted to hide a row in excel 2010 when either a date is imputed in up to 3 cells (C2,E2,G2)or the cell next to the Date Column is "N/A". We have a spreadsheet at work where we track the status of job tickets and when it is complete in addition we can have up to 2 or 3 sub Contractors invoices attached to the same Job or in some cases no Sub contractors invoices, I would like to hide the rows when a Job is complete and when all the sub contractors invoiced are received if there are any.

In the example below rows 2 and 4 would be hidden as all information has be collected and processed

is there a macro that can be set up to hide rows and make them visible when needed based on values in the example below?

Thanks in advance

	A	B	C	D	E	F	G
1	address	ticket status	Date Processed	Flooring Invoice	Date Processed	Gas Invoice	Date processed
2	1 New Street	Complete	10/4/2012	N/A		Yes	11/4/2012
3	3 New house	Not Complete		yes	9/4/2012	Yes	11/4/2012
4	5 old street	Complete	20/4/2012	Yes	22/5/2012	N/A

<tbody>
</tbody>

 

[rjwebgraphix]

Sub hiderows()

Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr

    If IsDate(Cells(i, "C")) Then
        If Cells(i, "D") = "N/A" Or IsDate(Cells(i, "E")) Then
            If Cells(i, "F") = "N/A" Or IsDate(Cells(i, "G")) Then
                Rows(i).Hidden = True
            End If
        End If
    End If

Next i

End Sub

Right click the tab, click view code.
When Editor opens at the top click Insert then Module
Paste in module and run it.

 

[craig0357]

Thank You rjwebgraphix .. that works just perfect. Is the a way to create a command button so I could hide or not hide rows using your formula?

thank you again

 

[rjwebgraphix]

Here's the way I would do it, there may be other methods that are easier, but this is just my way.

NOTE: These instructions are written as things are in XL03. Any other version of excel could be different.

Right click any tab and then click "View Code"
When the VBA editor opens at the top click "Insert" then click "Module"
Now that you have a new module - Copy and paste the entire code into the module.
If your new module does not have the words "Option Explicit" at the very top. Then add those words on a line by itself at the very top and paste the code below that line.

The key is to make sure its in a module and not in the code for a sheet. then....

Come back to your Excel window, at the top of the window click "View" then hover the mouse over "Toolbars"
Now Click on "Forms"

The Forms toolbar should now be visible. I usually dock it at the top, but if you're only going to use it once, you can leave it undocked.

On the Forms toolbar, the icon you want to create a button is the one that looks like a gray rectangle.
Click the rectangle, and now you want to treat it like you're drawing a rectangle on your sheet. Start in the top left of where you want it and then drag to the bottom right till its the size you want it.

Once you have the button created. It should pop up with a window to assign a macro. Just assign it the macro you pasted in, which in the case of the above macro, its named "hiderows"

You can change the text on the button by right clicking the button and selecting "Edit Text"
Name your button then click off the button and on to a blank area on the sheet and now you're set to run it. Just click the button.

However, If you are putting this button onto a sheet of its own and not on the sheet the code is being ran on, then we'll need to modify the code to run on the proper sheet.

If you need to do this, tell me what the sheet name is and I'll modify the code to fit.

Side note about Option Explicit. If your new module did not have Option Explicit at the top, then I would suggest turning it on. To do this you....

In the VBA editor, at the top of the screen click "Tools" then "Options"
When the options window comes up, look for the line that says, "Require Variable Declaration" and check it.

Click OK and now anything new you do with VBA will always have Option Explicit at the top.

 

[craig0357]

Thanks for you help once again .. works really well, I created a macro so i could unhide rows with a push of a button placed in the sheet too. thanks for pointing me in the right direction