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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
But the autofilter isn't automatic like the script. If I changed the value being filtered out, I would have to click on the values in the dropdown again to get the page to refilter. The VBA was getting us closer because it at least hid the right rows when I went to the sheet. It just didn't unhide the "Y"s that became "N"s since my previous visit to the sheet.
 
Upvote 0
On 2002-03-06 13:25, jeffmfrank wrote:
But the autofilter isn't automatic like the script. If I changed the value being filtered out, I would have to click on the values in the dropdown again to get the page to refilter. The VBA was getting us closer because it at least hid the right rows when I went to the sheet. It just didn't unhide the "Y"s that became "N"s since my previous visit to the sheet.

Jeff, did you paste both sets of code in your worksheet? I tested it and it worked okay for me.

Let me know,
 
Upvote 0
Again, it works great, but only in one direction - hiding. It needs to evaluate the hidden cells each time and unhide those that have changed to "N"s from "Y"s since the previous visit.
 
Upvote 0
But the data can be refiltered in VBA.

Range("Z19:AB31").Select
Selection.AutoFilter Field:=2, Criteria1:=">0"

Is a piece of code I use to filter a range for a pie chart before I export it. Field is the column in the range you want to filter on.

_________________
Give a man a fish and he'll eat for a day.
Teach that man to fish and he'll complain about having to work for what he once got free.
This message was edited by Steve Hartman on 2002-03-06 13:37
 
Upvote 0
On 2002-03-06 13:34, jeffmfrank wrote:
Again, it works great, but only in one direction - hiding. It needs to evaluate the hidden cells each time and unhide those that have changed to "N"s from "Y"s since the previous visit.

Well it works okay for me (a puzzle). How about this code which you insert in the Workbook:

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").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
Application.ScreenUpdating = True
End Sub

You can paste this in to the Workbook's code by right-clicking on the workbook in the Project Explorer window in the VBA Editor (usually this window is the top left one). Does this work?
 
Upvote 0
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


I've tried each and both in the worksheet code. It works great to hide, but doesn't seem to unhide when the value in a hidden cell changes. Is this routine evaluating hidden T cells on activation of the worksheet? (this sounds like biology) I don't think it is because I can't get an automatic unhide to occur when I change the data that results in a "Y" changing to an "N". Thanks for your help.
 
Upvote 0
How about this:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub

I assumed that the value being changed was going to be in the same row as the value in column T being evaluated (I know, I know....you know what happens when you assume!)

One note, this has the potential to become real slow if you have a lot of data in column T (it is evaluating each cell every time a change is made to the worksheet).

Regards,
 
Upvote 0
BTW, Your assumption was correct. If T4="Y" row 4 should be hidden. If T8="N" row 8 should not be hidden. On the first use, assume T9:T12 are all "Y". They will all be hidden when I activate the sheet, assuming the 2 step code given earlier (which works fine for this.) Then due to data entered in another sheet, T10 changes from "Y" to "N". When I activate Sheet1 again, row 10 is still hidden. The code must not be evaluating T10.? Any suggestions. (The last suggestion worked just like the others when I changed it to a worksheet activation rather than a change.) Thanks again.
 
Upvote 0
On 2002-03-06 15:28, jeffmfrank wrote:
BTW, Your assumption was correct. If T4="Y" row 4 should be hidden. If T8="N" row 8 should not be hidden. On the first use, assume T9:T12 are all "Y". They will all be hidden when I activate the sheet, assuming the 2 step code given earlier (which works fine for this.) Then due to data entered in another sheet, T10 changes from "Y" to "N". When I activate Sheet1 again, row 10 is still hidden. The code must not be evaluating T10.? Any suggestions. (The last suggestion worked just like the others when I changed it to a worksheet activation rather than a change.) Thanks again.

Boy, I must be sleeping at the computer today. Use this code (from an earlier posting):
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").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
Application.ScreenUpdating = True
End Sub

and change the references to "Sheet1" to whatever your sheet is named.
:)

I think we're there!
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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