VBA to hide a row

jeffmfrank

Board Regular
Joined
Feb 28, 2002
Messages
72
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0
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:
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top