Hide/unhide rows
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Hide/unhide rows

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide/unhide rows

    hi All,
    I have a problem with the workbook I work on. In one Tab (Tab1) with command buttons (Names A,B,C and D). The second tab is a table with a data for A, B, C and D. I want to hide all rows with 0 in column C in Tab2 if I press command button 1

    I have a code which works in the development mode but doesn't work whe nI actually use Command buttons:

    Dim r As Long
    For r = 3 To 35
    If Cells(r, 3) = 0 Then
    Rows(r).EntireRow.Hidden = True
    Else
    Rows(r).EntireRow.Hidden = False
    End If
    Next r

    Any help will be much appreciated.

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    If r = 1 which row 1 are you refering to? Every sheet has a row 1. You have to tell excel which row. For example use a with statement:

    Code:
    Dim r As Long
    With Sheets("Sheet1")
        For r = 3 To 35
            If .Cells(r, 3) = 0 Then
                    .Rows(r).EntireRow.Hidden = True
                Else
                    .Rows(r).EntireRow.Hidden = False
            End If
        Next r
    End With
    The reason it worked in testing is that you would have been on the correct sheet and if the sheet is not included in Rows, Range, Cells etc then excel ,by default, will use the active sheet.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,934
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide/unhide rows

    Another option is
    Code:
    Sub karolina1406()
       Dim Rw As Long
       With Sheets("Sheet2")
          For Rw = 3 To 35
             .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
          Next Rw
       End With
    End Sub
    Change value in red to suit.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Apr 2016
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/unhide rows

    thank you for this. I applied this code to the command button A and B code which is in Tab1. but I specified that hidden rows must be in Tab 2. It works ok for the first "Click" e.g. when all the rows in Tab2 are unhidden and I click Command Button A n Ta1, but then, when I come back to Tab1 to click Command button B - it doesn't hide any new rows

  5. #5
    New Member
    Join Date
    Apr 2016
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/unhide rows

    Quote Originally Posted by karolina1406 View Post
    thank you for this. I applied this code to the command button A and B code which is in Tab1. but I specified that hidden rows must be in Tab 2. It works ok for the first "Click" e.g. when all the rows in Tab2 are unhidden and I click Command Button A n Ta1, but then, when I come back to Tab1 to click Command button B - it doesn't hide any new rows
    ok, just realised that I would have to click Required command button twice to relevant rows be hidden. Any idea why?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,934
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide/unhide rows

    What are the other buttons doing?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Apr 2016
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/unhide rows

    Quote Originally Posted by Fluff View Post
    What are the other buttons doing?
    This same function. Basically, I want a user to open the spreadsheet and he/se will land on the page with 3 (for now) Command buttons: John, Anna, Chis. Tab2 is a table were I have a list of courses each of them. I want them to see only courses relevant to them. When John clicks "John" command button on Tab1 - I want him to be taken to Tab2 and the table to display rows with only John's course.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,934
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide/unhide rows

    In that case can you please post the code you are using.
    Originally you wanted to hide rows that had a 0 in col C, but that doesn't seem to tie with what you are now saying.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Apr 2016
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide/unhide rows

    Quote Originally Posted by Fluff View Post
    In that case can you please post the code you are using.
    Originally you wanted to hide rows that had a 0 in col C, but that doesn't seem to tie with what you are now saying.
    sorry for confusion. I applied supporting measure in col C as exce formula: if($D$3=$e4 ,1,0). Hence I was filtering column C. Whole Command Button cod for John (CB1):
    Code:
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Dim xls As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks(1 To 12) As Excel.Worksheet
    Set xls = Excel.Application
    Set wkb = xls.ThisWorkbook
    Set xls = Excel.Application
    Set wkb = xls.ThisWorkbook
    Set wks(1) = wkb.Sheets("Welcome")
    Set wks(2) = wkb.Sheets("Courses")
    
    Sheets("Welcome").Visible = True
    Sheets("Courses").Visible = False
    
    wks(2).Unprotect "kk"
    wks(2).Range("e1").Value = wks(1).Range("d7")
    Dim r As Long
    With Sheets("Courses")
    For r = 3 To 35
        .Rows(r).EntireRow.Hidden =False
    If .Cells(r, 3) = 0 Then
        .Rows(r).EntireRow.Hidden =True
    End If
        Next r
    wks(2).Protect "kk"
    End With
    
    ActiveSheet.DisplayAutomaticPageBreaks = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    End Sub
    Last edited by Fluff; Jul 18th, 2019 at 09:24 AM. Reason: code tags

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,934
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide/unhide rows

    The reason you had to click the button twice, is col C has a formula, but you have turned calculation off.
    You also have a lot of redundant code. Try
    Code:
    Sub karolina1406()
       Dim Ws As Worksheet
       Dim Rw As Long
       
       Set Ws = Sheets("Welcome")
       Ws.Visible = True
       
       With Sheets("Courses")
          .Visible = False
          .Unprotect "kk"
          .Range("E1").Value = Ws.Range("d7")
       
          For Rw = 3 To 35
             .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
          Next Rw
          .Protect "kk"
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •