Results 1 to 7 of 7

Thread: Creating a macro to hide all rows containing currently selected values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating a macro to hide all rows containing currently selected values

    I am hoping to create a way to hide rows on multiple sheets based off the text in the currently highlighted cell. So, for instance if I am on Sheet 1 and I am highlighting a cell containing "John Smith", I want to hit a button (from developer inserts) and it will hide all rows (across all 5 sheets) if their column c contains "John Smith".

    Is this sort of thing even possible? I am incredibly new to creating macros myself, so I am not sure if this is beyond the limits of excel. Perhaps I would need a add-in to be able to do this?

    Thanks!

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a macro to hide all rows containing currently selected values

    Additionally, I want to be able to do this multiple times with different cells, while still maintaining the previously hidden rows.

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,248
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Creating a macro to hide all rows containing currently selected values

    Quote Originally Posted by MisterBird View Post
    ...if their column c contains "John Smith".
    When you say "contains", does that mean "John Smith" is in the cell all by itself or could it be in the cell mixed in with other text?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a macro to hide all rows containing currently selected values

    Yes, "John Smith" would be in the cell all by itself.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,248
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Creating a macro to hide all rows containing currently selected values

    Quote Originally Posted by MisterBird View Post
    Yes, "John Smith" would be in the cell all by itself.
    Give this macro a try...
    Code:
    Sub HideRows()
      Dim Text As String, WS As Worksheet
      Text = ActiveCell.Value
      On Error Resume Next
      Application.ScreenUpdating = False
      For Each WS In Worksheets
        With WS.Columns("C")
          .Replace Text, "#N/A", xlWhole, , False, , False, False
          .SpecialCells(xlConstants, xlErrors).EntireRow.Hidden = True
          .Replace "#N/A", Text
        End With
      Next
      Application.ScreenUpdating = True
      On Error GoTo 0
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a macro to hide all rows containing currently selected values

    Rick,

    I appreciate your effort, but when I gave it a try it hid every row with text in it.

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a macro to hide all rows containing currently selected values

    Actually, after looking through the macro I realized I had mistakenly told you column "C", when it was actually column "D". After I changed that in the macro it works great! Thank you so much

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
  •