Hide/unhide rows
Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Thread: Hide/unhide rows

  1. #21
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    Sorry yes what i provided was based on the other macro. It will need altering if you are to use it elsewhere. Ill put some variables in it to make it easier. Hold fire. You can then alter to suit any range.

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

    Default Re: Hide/unhide rows

    Here goes. You need to alter the variables fr, lr and col to suit what you are testing for 0.

    fr meaning first row of the test data, lr the last row and col the column number

    Code:
    Dim i As Long, arr, rng As Range, fr As Long, lr As Long, col As Long
    
    fr = 3
    lr = 35
    col = 3
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet2")
        .Rows(fr & ":" & lr).Hidden = False
        arr = .Range(.Cells(fr, col), .Cells(lr, col))
        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 + fr - 1, 1))
                Else
                    Set rng = .Cells(i + fr - 1, 1)
                End If
            End If
        Next
    End With
    
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    
    Application.ScreenUpdating = True

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

    Default Re: Hide/unhide rows

    Well, I have done something horribly wrong as it doesn't work at all! I have a command button which I want a code for. What is should then do is to hide all the rows with "0" in Column A in Sheet 1 and all rows with "0" in column C in sheet 2. My complete code is as below:

    Dim i As Long, arr, rng As Range, fr As Long, lr As Long, col As Long

    fr = 3
    lr = 500
    col = 3
    Application.ScreenUpdating = False
    With Sheets("Sheet 1")
    .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 + fr - 1, 1))
    Else
    Set rng = .Cells(i + fr - 1, 1)
    End If
    End If
    Next
    End With
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    With Sheets("Sheet 2")
    .Rows("3:150").Hidden = False
    .Range("e1").Value = Sheets("Summary").Range("d7")
    arr = .Range(.Cells(3, 3), .Cells(150, 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 + fr - 1, 1))
    Else
    Set rng = .Cells(i + fr - 1, 1)
    End If
    End If
    Next
    End With
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    Application.ScreenUpdating = True
    Sheets("Sheet 1").Select

    End Sub

    what am I doing wrong??

  4. #24
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    That code isnt what i last provided so try using that and altering the fr, lr and col.

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

    Default Re: Hide/unhide rows

    sorry, I am more than a Blonde now. The code you provided works fine if I use it for One sheet only. I thought that I would have to copy and paste and alter this same code for sheet 2 - hence my long code above.

  6. #26
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    If you immedaitely want to use it for another sheet then you need to do a bit of cleanup inbetween and change the fr, lr and col for the new sheet. In the example i just used the same sheet twice but gives you a clue

    Code:
    Dim i As Long, arr, rng As Range, fr As Long, lr As Long, col As Long
    
    
    Application.ScreenUpdating = False
    
    '************first block
    fr = 3
    lr = 35
    col = 3
    
    With Sheets("Sheet2")
        .Rows(fr & ":" & lr).Hidden = False
        arr = .Range(.Cells(fr, col), .Cells(lr, col))
        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 + fr - 1, 1))
                Else
                    Set rng = .Cells(i + fr - 1, 1)
                End If
            End If
        Next
    End With
    
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    '***********end of first block
    
    'clean up before next block
    Erase arr
    Set rng = Nothing
    
    '**********second block
    fr = 3
    lr = 35
    col = 3
    
    With Sheets("Sheet2")
        .Rows(fr & ":" & lr).Hidden = False
        arr = .Range(.Cells(fr, col), .Cells(lr, col))
        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 + fr - 1, 1))
                Else
                    Set rng = .Cells(i + fr - 1, 1)
                End If
            End If
        Next
    End With
    
    If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    '*********end of second block
    
    Application.ScreenUpdating = True
    It would be possible to do this in a loop if there are lots of sheets but lets not complicate things too much!

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

    Default Re: Hide/unhide rows

    You are a real star. Thank you very much for this. Al works very quick as as I needed...... thank you for your patience with me. I am VA self-taught and I only learn by using correct macros.

  8. #28
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,509
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hide/unhide rows

    You will get there with practice. Ive never had anyone teach me anything formally about excel and VBA. All done from use and reading examples.

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
  •