Want to hide all rows that contain a value other than one described

[christy.payne]

EXAMPLE:
A B C
1 SCL DATE INFO
2 TSD DATE INFO
3 SB DATE INFO

Is it possible to run a macros, or create a button, so that I run and/or click to hide all rows but those that display SCL in column A. Then later hide everything but the TSD. Then later hide everything but the SB, etc. There are up to 50 different hedings that i need to work with, so if possible, please respond in a way that tells me how to apply this to much more than just the 3. Further, I though another option would be to use conditional formatting to turn anything that displays "False" or "0" (depending on formulas) to have column height=0, then run a macros to turn anything into a value other than the one I want to display into false, which would then go to column height=0 (ie, not display). However, I haven't quite sorted this out yet....

Thank you in advance!

[kelleytr]

How is what you are trying to accomplish different than using a filter?

[christy.payne]

Thank you for asking - perhaps I should have added that detail previously. For a data set that is used by many peple, most of which use computers and/or data little, I need a quick, easy way for them to click and view specific data. In the past I have seen schedulers spends thousands of dollars on creating an excel sheet that has buttons at the top with people's names, and then add complicated formulas in hidden columns to indicate whether that information applies to them, so that basically a person can click on the button on the top with their name on it and only the data relevant to them will display (this reduces really ANY steps for them, like learning how to run a filter, because they are not 'computer people'). I'm wondering if there is a simpler, complicated and time consuming way to write this so that they can still do that - either click a button or enter a word/value in single cell, which will ultimately make the data set only display certain rows. Running a filter is certainly an option, but I was trying to determine if there are more options....?

[kelleytr]

Hi Christy,

What are you are trying to accomplish is possible and the formulas wouldn't be too complicated. The disadvantage in using personalized buttons with names on them is that if those people leave then you will have to clean up the individual button (remove it). If new people enter the business then you will have to create a new button and reassign code to it. Both of those maintenance activities seem time consuming.

As the other member suggested, if you prompt the user for a variable and allow them to enter it then they can supply the vba the code it needs to filter on.

If you are still interested in using buttons I can assist - just let me know.

[tigeravatar]

christy.payne and kelleytr,

Welcome to the forum!
christy, I agree with kelley that a filter is the easiest way to go.
However, if you really want a VBA solution that you can assign to a button, then something like this should suffice:

Sub tgr()
    
    Dim strFind As String
    
    strFind = InputBox("Show only rows where column A contains...", "Search Term")
    If Len(strFind) = 0 Then Exit Sub   'Pressed cancel
    
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Cells.EntireRow.Hidden = False
    
    Intersect(ActiveSheet.UsedRange, Columns("A")).AutoFilter 1, "*" & strFind & "*"
    
End Sub

[christy.payne]

Everyone - thank you for the responses! Please understand that this sort of language is very new to me, so please be clear about where exactly I need to add these commands, as I will be struggling through them. I speak some html and xhtml - but anything else is new!

tigeravatar: With regards to my 'newness' can you briefly describe for me where all I would need to add this code and, if you could, distinguish between what is actual code and which are variables that I need to edit? Maybe with italics or something? Once I do it once I am sure it will makes heaps of sense, but I am still at the bottom of the learning curve!

Kelleytr: I am still interested. The example I have seen in the past used people's names, however, I was going to use a generalized 'product name' which would not change per project (which is every 1-3 years), but thank you for the warning! If it is possible to add the buttons I think it would be easiest, but ability for me to add additional buttons is going to be key, I think.

Thanks again to both of you!

[tigeravatar]

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one tgr)

Alternately, instead of pressing ALT+F8 to run the macro, you could create a Form button and assign a macro to it.

[kelleytr]

Hi Christy,

Our first step will be to create a vba button with some simple functionality. Once you are comfortable with that we can go a little deeper in solving your solution.

1. Create a button. Go to your "Developer" tab within the top ribbon. Don't see it? Go to "File" the click "Options", select "Customize Ribbon" on the left and the check off the "Developer" box. You should now be able to see a new ribbon option - "Developer".

2. Within the "Controls" section of the "Developer" ribbon select "Insert" and in the drop down select the Active X Command Button.

3. Now that you have a button you will want to give it some code. To get to the VBA editor press alt + F11. A new separate screen should appear.

4. Go back to your excel spread sheet. In the Developer ribbon make sure that the "Design Mode" button is clicked. Once you are in design mode, double click on the button you created. This should open up the editor to the correct area that you will enter your code in.

At this point you should see code that looks like this (or very similar):

Private Sub CommandButton1_Click()

End Sub

5. Now its time to write some test code. Write in the code so that it reads as the following:

Private Sub CommandButton1_Click()

MsgBox "Hello Christy"

End Sub

6. Go back to your excel sheet with the button and leave Design Mode if you are still in it. Test the button by clicking it. It should open up a message box that says "Hi Christy".

I'm not sure of you skill level but based on your previous posts it sounds like this may be a good pace. If this is too slow let me know and I can leave out some of the obvious details. If you are able to create this button and message then we can move on to the next step. Let me know when you are ready.

[christy.payne]

All set - that worked brilliantly. I will wait on further details form you, and thanks again!

[kelleytr]

Hi Christy,

I'm glad to see that first part worked out. Now it is time to replace the MsgBox "Hello Christy" with your solution code.

First lets make a new workbook. In "Sheet1" create a table with some data starting at cell A6:

table1_C.PNG

Now let's create two buttons (like you did in the previous post). Once they are created, put them at the top and edit their properties in the Visual Basic Editor (to get to the editor press alt+f11). Don't see the properties for the buttons? Go to "View" and then "Properties Window" to open the properties window.

You can locate the names Command Button1 and Command Button2 in the properties window under (Name)

For the button "Command Button1" we will set the following properties:
-Caption: Hide SCL Rows
-Height: 30
-Width: 180

For the button "Command Button2" we will the set the following properties:
-Caption: Unhide All Rows
-Height: 30
-Width: 180

Align the buttons near the top. Your worksheet should now look similar to this:

buttons1_C.PNG

Now it is time to add some code to your buttons.

Let's add code to the Hide SCL Rows button (Command Button1). Go into Excel and double click on the button (make sure you are in Design Mode).

Now you will enter code in it to look like this:

Private Sub CommandButton1_Click()

    With Worksheets("Sheet1")
    
        Dim intLastRow As Integer
        Dim rngColumnA As Range
        Dim cellColumnA As Range
        
        intLastRow = .Cells(1048576, 1).End(xlUp).Row
    
        Set rngColumnA = .Range(.Cells(7, 1), .Cells(intLastRow, 1))
        
        For Each cellColumnA In rngColumnA
            
            If cellColumnA.Value = "SCL" Then
                cellColumnA.EntireRow.Hidden = True
            End If
            
        Next cellColumnA
    
    End With

End Sub

There is a lot going on here. I'll break down each step:

Code:

With Worksheets("Sheet1")
...
End With

"With Worksheets..." code allows you to trap whatever code you are writing within the worksheet specified. In this case we are working with Sheet1. Now anytime we refer to cells or ranges the application will understand we are referring to only those found within Sheet1.

        Dim intLastRow As Integer
        Dim rngColumnA As Range
        Dim cellColumnA As Range

Dim is short for dimension. Dimensioning a variable or I believe some call it "instantiating" simply allows you to establish what type of variable it is that you are creating.

An integer is a whole number and a range is a collection of cells (i.e., Cells A1 through A10).

intLastRow = .Cells(1048576, 1).End(xlUp).Row

Here we are setting a value to the variable we just declared. We are saying that this number intLastRow is equal to the last cell in column A.

.Cells(1048576, 1).End(xlUp).Row is the code that we use to locate the last row in column A. What it does is it goes to cell row 1048576 (the very last row in Excel) column 1 (first column) and then goes up until it gets to a value. Once it gets to a value it then captures the row that it is on. That is a lot to take in, I know. But it does allow you to get the last row and this is especially useful as the amount of your data changes.

Set rngColumnA = .Range(.Cells(7, 1), .Cells(intLastRow, 1))

Ranges must be "Set" using the "Set" keyword. Here we are setting our variable rngColumnA equal to the range of data we want to look through later. Notice that is uses a range that starts with row 7 column 1 and goes through our last row (we just set this in the last step) and column 1. Now that we know what range to look through we can begin a loop to check for a specific value.

For Each cellColumnA In rngColumnA
            
    If cellColumnA.Value = "SCL" Then
        cellColumnA.EntireRow.Hidden = True
    End If
            
Next cellColumnA

This is a For Each loop. It goes through each value in the range we created and asks a question, is the value of the cell I'm in equal to "SCL"? if it is then it will set the entire row for that cell as hidden. This is where your solution rests.

Let's now add some code to unhide all these rows in the second button. This is easy since we don't need to test any condition to know if we should hide the row or not. We will simply unhide all rows within the worksheet Sheet1.

Here is the code to do this:

Private Sub CommandButton2_Click()

    Worksheets("Sheet1").Rows.Hidden = False
    
End Sub

And that's it! If you have successfully entered the code you should be able to hide and unhide the rows at the click of the button. Using the code as a template you can also create more buttons for more rows to hide based off of potentially different criteria.

I know this was a lot to take in. Feel free to ask me any questions and I'll do my best to get back to you.

[christy.payne]

No - it all makes perfect sense. however, the command we created is doing the opposite of what I need. I would like to creat a button that when i click on "SCL" button it hides everything else (shows only SCL. Now i am wondering if the way to do this is add additional command to that button, for instance:

Private Sub CommandButton1_Click()

With Worksheets("Sheet1")

Dim intLastRow As Integer
Dim rngColumnA As Range
Dim cellColumnA As Range

intLastRow = .Cells(1048576, 1).End(xlUp).Row

Set rngColumnA = .Range(.Cells(7, 1), .Cells(intLastRow, 1))

For Each cellColumnA In rngColumnA

If cellColumnA.Value = "TSD" Then
cellColumnA.EntireRow.Hidden = True
End If

If cellColumnA.Value = "TSD" Then
cellColumnA.EntireRow.Hidden = True
End If

Next cellColumnA

End With

End Sub

[kelleytr]

Hi Christy,

Close! What you want to do is ask the question: Does the value I'm in not equal "TSD" ? If it does not then set the row to hidden.

Here is the code, it is very similar to what we had previously. We only change the "=" to a "<>"

 If cellColumnA.Value <> "TSD" Then
 cellColumnA.EntireRow.Hidden = True
 End If