Hiding Rows Based on Cell Content Macro

[Craig13_13]

Hello all

I have a list of ingredients and there is a column for each which looks up from another spreadsheet whether the item is currently in use or not (just through a vlookup).
Because this is quite a big list and the "in use" part varies what I would like to do is code a Macro that would go through and automatically hide any rows where this column states "NOT IN USE", it is column A if that is of any help.
I have no idea how to get it to hide rows based on the content of a cell in that row - any suggestions?!

 

[SteveO59L]

Why not use an autofilter ?

 

[VoG]

Try

Sub HideUnhide()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Rows(i).Hidden = Range("A" & i).Value = "NOT IN USE"
Next i
End Sub

 

[Craig13_13]

Whilst this is an option, for neatness and to make the spreadsheet as simple as possible for casual users I have this column hidden, just throwing this out there is there therefore a way to link an autofilter to another cell in an unhidden column?

 

[Craig13_13]

VoG that code is brilliant!!

Thanks for that

 

[ChristineJ]

I'm very new to using code in Excel. The question asked in this thread and the code that VoG has provided is exactly what I want to do:

Sub HideUnhide()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Rows(i).Hidden = Range("A" & i).Value = "NOT IN USE"
Next i
End Sub

However, I have right clicked on my sheet tab, selected View Code, and pasted in the code in red above. Yet I don't see the row hide when I type NOT IN USE into a clee in column A. Can you point me in the right direction?

 

[Peter_SSs]

The question asked in this thread and the code that VoG has provided is exactly what I want to do:

.. when I type NOT IN USE into a clee in column A.

Christine

My interpretation is that the original poster had formulas in column A, whereas you are typing into column A. This may make a difference to the best approach.

Can you outline your layout, data and requirements a bit more fully? Then somebody should be able to "point you in the right direction".

 

[ChristineJ]

In File1.xls, I am using code I found on another site that one of your MVPs wrote to allow a user to double click in a cell to make a checkmark appear. (It is actually the letter "a" in marlett font.) For my application, the checkmarks can be added in column A, rows 1 through 25, of Sheet1. The result is that based on user input, cells A1:A25 are either blank or contain the letter "a" if they are checked. Column B has text, Arial font, in each row. (See the bottom of this post for the MVP's code, in blue.) That works fine.

My goal is to link two Excel files. In File2.xls, when it opens, I want its Sheet1, B1:B25, to show the text from B1:B25 File1.xls, but only the rows where an "a" appears in Column A in File1.xls. The rows in Column A, File1.xls that are blank should be hidden in Sheet1, File2.xls.

Ideally, I would also want Sheet2 in File1.xls,when clicked on, be the same as File2.xls, Sheet1.

Using Excel 2003. Thanks, Chris

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Exit Sub
End If
End Sub