Results 1 to 10 of 10

Thread: Macro to hide rows ad hoc
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member detweiler's Avatar
    Join Date
    Aug 2013
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Macro to hide rows ad hoc

    I found this - -

    Worksheets(“Sheet1”).Range(“RowNumber1:RowNumber2”).EntireRow.Hidden = True

    - - and would work, but would have to modify it each time based on the worksheet I'm in. Yes, right-clicking is easy, but when there are hundreds or, and a couple of times, thousands, no so much.

    Is it possible to modify the current worksheet ( active.worksheet ? ) and be prompted for what rows I want to hide?

    Thanks for any guidance, help and insight.

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

    Default Re: Macro to hide rows ad hoc

    Would you always be hiding consecutive rows?
    - 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. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Macro to hide rows ad hoc

    Something like this?
    Code:
    Sub Hide_Rows()
      Dim sRws As String
      
      sRws = InputBox("Enter rows to be hidden, separated by a colon. eg 5:25 or for a single row just 3")
      On Error Resume Next
      Rows(sRws).Hidden = True
      On Error GoTo 0
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member detweiler's Avatar
    Join Date
    Aug 2013
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to hide rows ad hoc

    Yes, and can be in multiple places throughout a single worksheet.

  5. #5
    New Member detweiler's Avatar
    Join Date
    Aug 2013
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to hide rows ad hoc

    Thank you. Will give this a go and let you know how well it worked!

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

    Default Re: Macro to hide rows ad hoc

    How about
    Code:
    Sub detweiler()
       Dim Rws As String
       Dim Sp As Variant
       Dim i As Long
       Rws = InputBox("Please enter rows to hide like 3:20 or if multiple sections 3:20,25:30")
       If InStr(1, Rws, ",") > 0 Then
          Sp = Split(Rws, ",")
          For i = 0 To UBound(Sp)
             Rows(Sp(i)).Hidden = True
          Next i
       Else
          Rows(Rws).Hidden = True
       End If
    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

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Macro to hide rows ad hoc

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub detweiler()
       Dim Rws As String
       Dim Sp As Variant
       Dim i As Long
       Rws = InputBox("Please enter rows to hide like 3:20 or if multiple sections 3:20,25:30")
       If InStr(1, Rws, ",") > 0 Then
          Sp = Split(Rws, ",")
          For i = 0 To UBound(Sp)
             Rows(Sp(i)).Hidden = True
          Next i
       Else
          Rows(Rws).Hidden = True
       End If
    End Sub
    Probably don't really need those crossed out rows.

    .. or could be compacted to ..
    Code:
    Sub detweiler_v2()
      Dim Sp As Variant
      
      For Each Sp In Split(InputBox("Please enter rows to hide like 3:20 or if multiple sections 3:20,25:30"), ",")
        Rows(Sp).Hidden = True
      Next Sp
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member detweiler's Avatar
    Join Date
    Aug 2013
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to hide rows ad hoc

    Thank you for the input. The multiples option makes going through each worksheet quicker.

  9. #9
    New Member detweiler's Avatar
    Join Date
    Aug 2013
    Posts
    44
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to hide rows ad hoc

    Appreciate and enjoy the simplicity of the code sir. It works as I need it to, as I said to Fluff, it makes going through each worksheet quicker now.

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

    Default Re: Macro to hide rows ad hoc

    Glad we could help & thanks for the feedback.


    Quote Originally Posted by Peter_SSs View Post
    Probably don't really need those crossed out rows.
    Excellent point as always.
    - 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
  •