VBA to hide a row

[jeffmfrank]

I'm a VBA rookie so I apologize if this is a simple procedure. What kind of code should I use to have a sheet automatically hide rows where the value in the column T for that row is "Y"? Can this always be running so that if "Y" changes to "N", the row is no longer hidden? Thanks.

[Steve Hartman]

Does it have to be VBA? You could do this without VBA by using DATA VALIDATION.

[jeffmfrank]

how, i'm all ears for suggestions. Thanks!

[Steve Hartman]

Sorry, I meant Conditional formatting. Select the first cell of your first row of data. I'm assuming A1 for this example. Choose FORMAT > Conditional formatting.
Change Cell value is to Formula is and put =$T1="Y" in the formula box. then click on format and set the font color to the same as the background color for the cell. Click OK twice. Copy the cell and then paste Special Format to all the cells in that row you want to format.When there is a Y in column T the data in that row will be "invisible". You can copy the format to all the cells in your range.

This formula will only hide data when col T is = Y or y. If you only want it to be visible if the user puts an N in col T use =$T1<>"N" for the formula

[jeffmfrank]

but I actually need to hide the rows, not just the text in the row. The formula in the T column recognizes that there is nothing in the row accomplished with my if statement retuning a "Y" (or "N" if there is somehthing in the row and it shouldn't be hidden - ie should be printed.)

I need the sheet to shrink down horizontally as much as possible automatically - not just hide the text from view. Sorry I should have clarified. I wish conditional formatting included "Hide Row" as a condition, but it doesn't seem to. Thanks for your suggestion, though.

[Barrie Davidson]

On 2002-03-06 11:18, jeffmfrank wrote:
but I actually need to hide the rows, not just the text in the row. The formula in the T column recognizes that there is nothing in the row accomplished with my if statement retuning a "Y" (or "N" if there is somehthing in the row and it shouldn't be hidden - ie should be printed.)

I need the sheet to shrink down horizontally as much as possible automatically - not just hide the text from view. Sorry I should have clarified. I wish conditional formatting included "Hide Row" as a condition, but it doesn't seem to. Thanks for your suggestion, though.

Right click on your worksheet, select ViewCode and then paste this in to your sheet's code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Range("T" & Target.Row).Value
Case Is = "Y"
Target.EntireRow.Hidden = True
Case Is = "N"
Target.EntireRow.Hidden = False
End Select
End Sub

Regards,

[jeffmfrank]

thanks this is awesome and I think very close, but it only works if I manually enter "N" or "Y". The values "Y" and "N" in column T are determined when data is entered in another sheet. Should I have a Worksheet_Calculate instead of Worksheet_Change? How should I change this to work? Thanks again.

[Barrie Davidson]

On 2002-03-06 12:46, jeffmfrank wrote:
thanks this is awesome and I think very close, but it only works if I manually enter "N" or "Y". The values "Y" and "N" in column T are determined when data is entered in another sheet. Should I have a Worksheet_Calculate instead of Worksheet_Change? How should I change this to work? Thanks again.

Well, you could try these two sets of code:

Private Sub Worksheet_Activate()
For Each c In Range("T1", Range("T65536").End(xlUp).Address)
    Select Case c.Value
    Case Is = "Y"
        c.EntireRow.Hidden = True
    Case Is = "N"
        c.EntireRow.Hidden = False
    End Select
Next c
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Select Case Range("T" & Target.Row).Value
    Case Is = "Y"
    Target.EntireRow.Hidden = True
    Case Is = "N"
    Target.EntireRow.Hidden = False
    End Select
End Sub

Does this help you out?

[jeffmfrank]

This works in the hide direction. But if I add data on the data sheet, I also need the code to recognize that the value in T (although hidden) has changed from "Y" to "N" and then unhide the row. Thanks.

[Steve Hartman]

How about an Autofilter?