Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: VBA to hide a row

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

    Default

    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.

  2. #2
    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

    Does it have to be VBA? You could do this without VBA by using DATA VALIDATION.
    Veni, Vidi, Velcro - I came, I saw, I stuck around

    Taxation WITH representation ain't so hot either

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

    Default

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

  4. #4
    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

    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
    Veni, Vidi, Velcro - I came, I saw, I stuck around

    Taxation WITH representation ain't so hot either

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

    Default

    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.

  6. #6
    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 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,

    Barrie Davidson

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

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

    Default

    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.

  8. #8
    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 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?

    Barrie Davidson

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

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

    Default

    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.

  10. #10
    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

    How about an Autofilter?

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
  •