Solved macro to hide rows if 1 column has a specified value (excel)

January 7, 2013 at 05:13:26
Specs: Windows XP

Hi there,
I'm not used to VBA language so I couldn't use the comments of the replies on the same topic (May 2010) to try to write my own code.
So here is my request : in excel 2010, I would like to hide the rows (as from row 7) for which the column L has the value "Complete" (I've put a data validation list in column L that resume the status of the different actions written in the first column).
Is it also possible to get an "active" button to click on to hide & unhide the "completed" actions?

Thanks a lot in advance to anybody who could help me out.

Marie


See More: macro to hide rows if 1 column has a specified value (excel)

Report •


#1
January 7, 2013 at 15:18:36

Hi
Could you provide a link to the May 2010 topic?

But it sounds like "Filtering" will get you what you want. If you place a filter on col L and in the Filter drop down select everything but "Complete" it will hide all rows with "Complete" in Col L. To reverse it simply click on "Complete" in the Filter drop down and all the "Complete" rows will reappear.


Report •

#2
January 7, 2013 at 23:50:31

Hi AlteK,
the easiest way seems to use the filter method indeed but I have another excel file with this kind of macro and what I call "active buttons" on the excel sheet that allow the user to hide-unhide the "complete" tasks. The macro is not working though and I would like to understand how to do such a thing.

Here is the link to the topic from 2010 : http://www.computing.net/answers/of...

And here is the code from the macro that is not working (the rows and columns are not identical to my excel file but I want to do the same kind of thing).
Besides, I think there are 2 codes : one to hide-unhide the completed tasks and one to add a new task.

Private Sub CommandButton1_Click()
With Worksheets("Action Log")
If .AutoFilterMode Then
With .AutoFilter.Filters(8)
If .On Then
Selection.AutoFilter Field:=8
Else
Selection.AutoFilter Field:=8, Criteria1:="<>complete", Operator:=xlAnd
End If
End With
End If
End With
End Sub

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
With Worksheets("General")
If .AutoFilterMode Then
With .AutoFilter.Filters(8)
If .On Then
Selection.AutoFilter Field:=8
End If
End With
End If
End With
Sheet1.Activate
Set rglast = Sheet1.Range("B1").SpecialCells(xlCellTypeLastCell)
i = rglast.Row
Rows(i).Select
Selection.Copy
Rows(i + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$B$2:$I$" & i + 1
Rows(i + 1).RowHeight = 38.25
Cells(i + 1, 2) = Cells(i, 2) + 1
Cells(i + 1, 3) = Date
Cells(i + 1, 4).Select
' With Worksheets("General")
' If .AutoFilterMode Then
' With .AutoFilter.Filters(8)
' Selection.AutoFilter Field:=8, Criteria1:="<>complete", Operator:=xlAnd
' End With
' End If
' End With
Application.ScreenUpdating = True
End Sub

Thanks a lot in advance for your help.

Marie



Report •

#3
January 8, 2013 at 10:37:43
✔ Best Answer

Hi Linth

Your first macro will do what you want however it is looking for the 8th column that has filtering on it i.e. if your first filtered column is in B then it is working on Col I. So, if you want it to work on Col L AND assuming you have filters set on Cols B to L then you'll need to change the code wherever it says Field:=8 or Filter(8) to 11 like this...

Private Sub CommandButton1_Click()
    With Worksheets("Action Log")
        If .AutoFilterMode Then
            With .AutoFilter.Filters(11)
                If .On Then
                    Selection.AutoFilter Field:=11
                Else
                    Selection.AutoFilter Field:=11, Criteria1:="<>complete", Operator:=xlAnd
                End If
            End With
        End If
    End With
End Sub

If you attach this code to a button it will toggle between showing and hiding the rows with 'Complete"


Report •

Related Solutions

#4
January 10, 2013 at 07:53:22

Thanks AlteK for the explanation, it's working now; I just changed the 11 to 12 because my filters start from the first column in my document.
I also found how to make an active button, remains one problem : link the macro to hide/unhide with the button...I don't know how to attach them together.

Sorry for being so bad at VBA code...
and thanks a lot for helping me out!


Report •

#5
January 10, 2013 at 11:00:33

Hi

No need to apologise - that's why we're here.

With respect to your question about linking macros to buttons - it's a bit difficult to explain without seeing how you are set up so let's create a new button.

Click on the Developer ribbon - if you don't have that then you'll need to go to Advanced options and enable the developer ribbon.

In the Developer ribbon click on Insert
Click on the first icon (Command Button) under ActiveX Controls
In the worksheet, left click and drag to create the button (you can adjust the size later)
Now double click on the new button
It will open up a new VBA procedure that will start with

Sub CommandButtonX_Click()

(the "X" will depend on how many other buttons you have)
Take your adjusted code except for the first and last lines and paste it into the new procedure
Go back to your newly created button and Right Click on it
Click on CommandButton_Object / Edit
You will now be able to delete the text in the button and type in whatever you want e.g Filter Completed
Click on Design Mode to turn it off and test your new button
To get rid of old buttons click on design mode again and highlight unwanted buttons and press delete
You can also resize the new button by clicking on it and dragging from the corners.

Give this a try and come back if you get stuck anywhere.


Report •

#6
January 11, 2013 at 08:12:07

Many many thanks Altek!!!
Everything is working perfectly!

Cheers


Report •


Ask Question