Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: VBA to hide a row

  1. #11
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 13:16, Steve Hartman wrote:
    How about an Autofilter?
    I think this is the way to go! Nice job Steve, it's so easy for me to miss the obvious sometimes
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #13
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #15
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #16
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #17
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #18
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  9. #19
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #20
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •