Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: Hide/unhide rows

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

    Default Re: Hide/unhide rows

    that's great! It now works but I had to switch off this automatic calc.... Thank you very much for a help. Really useful learning

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,050
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Hide/unhide rows

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Hide/unhide rows

    hi again
    I was wondering if you know any tricks/hints/codes which would speed up my macro. I have applied above to a different model and it works perfectly fine other than it's very slow

  4. #14
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,804
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hide/unhide rows

    Try using

    Code:
    Sub karolina1406()
    Dim Rw As Long
    Application.ScreenUpdating = False
       With Sheets("Sheet2")
          For Rw = 3 To 35
             .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
          Next Rw
       End With
    Application.ScreenUpdating = True
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #15
    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 Michael M View Post
    Try using

    Code:
    Sub karolina1406()
    Dim Rw As Long
    Application.ScreenUpdating = False
       With Sheets("Sheet2")
          For Rw = 3 To 35
             .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
          Next Rw
       End With
    Application.ScreenUpdating = True
    End Sub
    Already got this and its still slow The only thing what speeds it up at the moment is if I switch off automatic calc however that then means that the command button needs to be pressed twice to work

  6. #16
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,804
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hide/unhide rows

    You don't have any worksheet_events running as well, do you ??
    If you turn autocalc off and back on at the end of the code shouldn't that then run correctly ??
    Code:
    Sub karolina1406()
    Dim Rw As Long
      with Application
       .ScreenUpdating = False
       .calculation=xlmanual
      end with
       With Sheets("Sheet2")
          For Rw = 3 To 35
             .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
          Next Rw
       End With
      with Application
        .ScreenUpdating = true
        .calculation=xlautomatic
      end with
    End Sub
    Last edited by Michael M; Jul 23rd, 2019 at 03:32 AM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #17
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    You could use an array then you dont need to be hiding/unhiding rows 30 odd times but all at once. Should be quicker.

    Code:
    Dim i As Long, arr, rng As Range
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
        .Rows("3:35").Hidden = False
        arr = .Range(.Cells(3, 3), .Cells(35, 3))
        For i = LBound(arr) To UBound(arr)
            If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
                If Not rng Is Nothing Then
                    Set rng = Union(rng, .Cells(i + 2, 1))
                Else
                    Set rng = .Cells(i + 2, 1)
                End If
            End If
        Next
    End With
    
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    
    Application.ScreenUpdating = True

  8. #18
    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 steve the fish View Post
    You could use an array then you dont need to be hiding/unhiding rows 30 odd times but all at once. Should be quicker.

    Code:
    Dim i As Long, arr, rng As Range
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
        .Rows("3:35").Hidden = False
        arr = .Range(.Cells(3, 3), .Cells(35, 3))
        For i = LBound(arr) To UBound(arr)
            If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
                If Not rng Is Nothing Then
                    Set rng = Union(rng, .Cells(i + 2, 1))
                Else
                    Set rng = .Cells(i + 2, 1)
                End If
            End If
        Next
    End With
    
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    
    Application.ScreenUpdating = True
    sorry for being a Blonde but which part of this code does hide all the rows if in column C I have 0 "zero"?

  9. #19
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    This bit:

    Code:
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True

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

    Default Re: Hide/unhide rows

    great, that's fine... I tried to apply the same code for another tab (within this same command button) which is called "Pogramme_Plan" and the filtering (0,1) is in column A from row 9. I applied below:

    With Sheets("Programme_Plan")
    .Rows("9:500").Hidden = False
    .Range("a7").Value = Sheets("Summary").Range("d7")
    arr = .Range(.Cells(3, 3), .Cells(500, 1))
    For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
    If Not rng Is Nothing Then
    Set rng = Union(rng, .Cells(i + 2, 1))
    Else
    Set rng = .Cells(i + 2, 1)
    End If
    End If
    Next
    End With
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True


    but I am getting an error at the

    Set rng = Union(rng, .Cells(i + 2, 1)) line... any idea why?

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
  •