1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel macro to hide rows if value does not match header

Discussion in 'Business Applications' started by johnm524, Jun 24, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. johnm524

    johnm524 Thread Starter

    Joined:
    Apr 22, 2009
    Messages:
    21
    I have a drop down list in D1 and what I need is a macro that will search for the selected value through column D and hide the rows that don't match. Think of it as like the autofilter function in excel.
     
  2. terabytecomputer

    terabytecomputer

    Joined:
    Apr 20, 2009
    Messages:
    94
    This should do it, if you don't mind filtering on the click of a button. It assumes there are no empty cells in column D. If there are, you'll have to build in logic for that.

    Private Sub CommandButton1_Click()
    Dim myRow As Integer
    dim myColumn as Integer

    myColumn=4 'Column D
    myRow = 2 'Starts search beginning on row 2

    Do While Cells(myRow, myColumn) <> ""
    If Cells(myRow, myColumn) = Cells(1, myColumn) Then
    Rows(myRow).Hidden = False
    Else
    Rows(myRow).Hidden = True
    End If
    myRow = myRow + 1
    Loop

    End Sub


    If you then want to display all rows again, use this with a command button:

    Private Sub CommandButton2_Click()
    Sheets(1).Rows.Hidden = False
    End Sub
     
  3. johnm524

    johnm524 Thread Starter

    Joined:
    Apr 22, 2009
    Messages:
    21
    Thanks for replying.

    Is there a way to do this without using the command button? Say the user selects from the drop down list in D2, can it automatically compare that value with the rest of column D and hide the rows that do not contain that value?
     
  4. terabytecomputer

    terabytecomputer

    Joined:
    Apr 20, 2009
    Messages:
    94
    Yes, there is. This will fire off whenever the sheet is changed though, so if you change other data, it will keep refreshing the list.

    Enter this in your code (you can remove the command button code):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRow As Integer
    Dim myColumn As Integer
    myColumn = 4 'Column D
    myRow = 2 'Starts search beginning on row 2
    Do While Cells(myRow, myColumn) <> ""
    If Cells(myRow, myColumn) = Cells(1, myColumn) Then
    Rows(myRow).Hidden = False
    Else
    Rows(myRow).Hidden = True
    End If
    myRow = myRow + 1
    Loop
    End Sub
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    To circumvent that, use this after the declarations:

    If Target.Address <> "$D$1" Then Exit Sub
     
  6. johnm524

    johnm524 Thread Starter

    Joined:
    Apr 22, 2009
    Messages:
    21
    Thanks for the help! One more thing though, I have merged cells in row 2,93,166,299,394,441,442. What do I need to add in order to avoid hiding them?
     
  7. terabytecomputer

    terabytecomputer

    Joined:
    Apr 20, 2009
    Messages:
    94
    Good call, bomb.

    john, add this statement immediately after the 'Do' line:

    Select Case myRow
    Case 2, 93, 166, 299, 394, 441, 442
    Exit Sub
    End Select
     
  8. johnm524

    johnm524 Thread Starter

    Joined:
    Apr 22, 2009
    Messages:
    21
    Thank you both so much! Everything works great now.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/837862

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice