Hide rows if they do not contain a "Y". (1 Viewer)

[Rhino999]

I'm looking for Macro that will help control inventory.
What I have is 190 rows and 10 columns.
Starting position is currently G4 ranging through P193.
If an item is in stock it will be flagged with a “Y”.
Whenever any single row of all 10 columns is either empty or blank, I would like to hide it. If possible, I would appreciate if the code could be written in a way in which it is easy to changed, to add additional rows and columns or to remove rows or columns. I would also like to be able to easily change the starting row and columns positions.

 

[qafself]

Why not just filter on the letter "Y"

 

[Rhino999]

Thank you for your response!
Well, I guess that is a solution, but it wasn't the one I was looking for, because I would adapt the macro to do other things also. I never use the Filter to see if it could work for something like that, but I will check that out.

 

[Rhino999]

I did check out the advanced filter, it's not something that I would want to use.It takes too much effort and time for people who are not familiar with how to run it. Additionally, it takes screen space and there are other worksheets that are all coordinated together with other rows and columns that have stuff on the same line. It just doesn't fit the situation. But thanks for your suggestion!

If anyone has a macro that can help me that would be great!
Thank you for your time.

 

[Brianwarnock]

The title of your question and its description are different, which is it, a blank row or a row without a Y in a given column?

Brian

 

[Rhino999]

Brian, it is a row without a "Y" in a given column.

I would prefer if the macro would be able to look for the letter "Y", instead of just counting if something is present in that row/column, this way I could use the macro in other isyuations with other letters down the road, like a "D" for discontinued or a #N/A which is returned when a Vlookup Function, doesn't find a match in a row.

Thank you for your help.

 

[Brianwarnock]

the code below checks that there is not a Y in column B ie 2, note that it is case sensitive.
To use just highlight the area then run the macro
Brian

Sub hiderows()
Dim colnum As Integer
Dim testvalue As String

colnum = 2
testvalue = "Y"

With ActiveWindow.RangeSelection
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
firstrow = lastrow - .Rows.Count + 1
End With

For r = firstrow To lastrow Step 1

If Cells(r, colnum).Value <> testvalue Then Rows(r).Hidden = True
Next r

End Sub

 

[Rhino999]

Thank you for responding

This needs to be run for multiple columns all at same time, can I just change the column 2 for B to a range of columns. ie column = G4987.

Additionally, was planning on just running this from a keystroke, I think it's always easier for the user and quicker when time is of the essence, than running it by scrolling and highlighting the area.

Can it be easily adapted to do that?

Again, thank you very much!

 

[Brianwarnock]

Hmm, I posted a version 2 on Wednesday , wonder where it is, still it was also a waste of time now that the requirements have changed. What are they by the way?
Hide a row if all of the columns G to P in the row don't have a Y or
Hide the row if any one of the columns G to P doesn't have a Y.

Brian

 

[Rhino999]

Brian, sorry for the confusion, that's my fault. The one thing I don't want to do is waste your time. So thanks again for trying to help me.

The criteria is as you stated,
"Hide the row, if any one of the columns G to P doesn't have a Y".

That is correct, if there are no "Y" 's on that row, in that range of columns, than the row gets hidden.

Thank you very much,
Rhino999

 

[Brianwarnock]

The criteria is as you stated,
"Hide the row, if any one of the columns G to P doesn't have a Y".

That is correct, if there are no "Y" 's on that row, in that range of columns, than the row gets hidden.

Sorry but that's a contradiction, your statement is equivalent to my

Hide a row if all of the columns G to P in the row don't have a Y

so that is what I will provide as it makes more sense.

Brian

 

[Brianwarnock]

This is designed to be flexible as you originally asked, the range and testvalue are input by the user. You can obviously modify it to make either constant if you wish.
The macro can be run many ways including a key combination as you also asked.

Brian

Sub hiderowsallnotY()
'Written by Brian Warnock Feb 2010
'This hides the row if all columns in the range <> Testvalue
'The Range and the Testvalue are provided by input box
'The range is in the form  B3:K45 and the testvalue is a string

Dim testvalue As String
Dim myrange As Range
Dim rcrange As String
Dim firstrow%, lastrow%, firstcol%, lastcol%

Set selectionrange = Application.InputBox(prompt:="enter range", Type:=8)
rcrange = selectionrange.Address(ReferenceStyle:=xlR1C1)
' find row and column numbers
firstrow = Mid(rcrange, 2, InStr(rcrange, "C") - 2)
lastrow = Mid(rcrange, InStrRev(rcrange, "R") + 1, InStrRev(rcrange, "C") - InStrRev(rcrange, "R") - 1)
firstcol = Mid(rcrange, InStr(rcrange, "C") + 1, InStr(rcrange, ":") - InStr(rcrange, "C") - 1)
lastcol = Mid(rcrange, InStrRev(rcrange, "C") + 1, Len(rcrange) - InStrRev(rcrange, "C"))

testvalue = Application.InputBox(prompt:="enter testvalue", Type:=2)

For r = firstrow To lastrow Step 1
Set myrange = Range(Cells(r, firstcol), Cells(r, lastcol))
    For Each c In myrange
    If c.Value = testvalue Then
    GoTo outerloop
    End If
    x = c.Value
    Next c
   Rows(r).Hidden = True ' all cols<>testvalue
outerloop:
Next r

End Sub

 

[HaHoBe]

Hi, Brian,

my suggestion for some minor adjustments

Dim rngSelection  As Range
Dim lngRowCounter As Long
Dim rngCell As Range
Dim strCheck As String
Dim lngCellValues As Long

'avoid run-time error on Cancel
On Error Resume Next
Set rngSelection = Application.InputBox("enter range", Type:=8)
'reset error-handling to system
On Error GoTo 0
'check, if a valid range has been seletced - mind Cancel
If Not myRange Is Nothing Then
    strCheck = Application.InputBox(prompt:="enter value to evaluate", Type:=2)
    With rngSelection
        Debug.Print "First row: " & .Cells(1).Row
        Debug.Print "First column: " & .Cells(1).Column
        Debug.Print "Last row: " & .Cells(.Cells.Count).Row
        Debug.Print "last column: " & .Cells(.Cells.Count).Column
        For lngRowCounter = .Cells(1).Row To .Cells(.Cells.Count).Row
            Set myRange = Range(Cells(lngRowCounter, .Cells(1).Column), Cells(lngRowCounter, .Cells(.Cells.Count).Column))
            For Each rngCell In myRange
                If rngCell.Value = strCheck Then Exit For
            Next rngCell
            Rows(lngRowCounter).Hidden = True ' all cols<>Y
        Next lngRowCounter
    End With
End If

Ciao,
Holger

 

[Brianwarnock]

Hi, Brian,

my suggestion for some minor adjustments

Ciao,
Holger

For "minor adjustments" read "improvements", yes I forgot, usually do these days, to check errors, and also never thought of the Exit For, and...

I do like the way you got the rows and column numbers, I thought that the must be a better way.

Brian

Ah Did some testing
1 the Exit For does not work as it takes you to the instruction after the Next which is
Rows(lngRowCounter).Hidden = True
so all rows end up hidden.

2 This
If Not myRange Is Nothing Then
should be
If Not rngSelection Is Nothing Then

3 You have not declared myrange ,ok that is not important if Optian Explicit is not used.

 

[HaHoBe]

Hi, Brian,

well, I didn´t test the code. Option Explicit has given me the points as well when I started to run it on a sample. So, here we are again with some minor adjustments:

Dim rngSelection  As Range
Dim lngRowCounter As Long
Dim rngCell As Range
Dim strCheck As String
Dim lngCellValues As Long
Dim blnHide As Boolean
Dim myRange As Range

'avoid run-time error on Cancel
On Error Resume Next
Set rngSelection = Application.InputBox("enter range", Type:=8)
'reset error-handling to system
On Error GoTo 0
'check, if a valid range has been seletced - mind Cancel
If Not rngSelection Is Nothing Then
    strCheck = Application.InputBox(prompt:="enter value to evaluate", Type:=2)
    With rngSelection
'        Debug.Print "First row: " & .Cells(1).Row
'        Debug.Print "First column: " & .Cells(1).Column
'        Debug.Print "Last row: " & .Cells(.Cells.Count).Row
'        Debug.Print "last column: " & .Cells(.Cells.Count).Column
        For lngRowCounter = .Cells(1).Row To .Cells(.Cells.Count).Row
            blnHide = False
            Set myRange = Range(Cells(lngRowCounter, .Cells(1).Column), Cells(lngRowCounter, .Cells(.Cells.Count).Column))
            For Each rngCell In myRange
                If rngCell.Value = strCheck Then
                    blnHide = True
                    Exit For
                End If
            Next rngCell
            Rows(lngRowCounter).Hidden = blnHide ' all cols<>Y
        Next lngRowCounter
    End With
End If

Ciao,
Holger

 

[Brianwarnock]

Hi Holger
Hope you don't mind but tested again and it now works with a minor correction to the blnHide settings.

Brian

For lngRowCounter = .Cells(1).Row To .Cells(.Cells.Count).Row
        blnHide = True
            Set myrange = Range(Cells(lngRowCounter, .Cells(1).Column), Cells(lngRowCounter, .Cells(.Cells.Count).Column))
            For Each rngCell In myrange
            
                If rngCell.Value = strCheck Then
                blnHide = False
                Exit For
                End If
            Next rngCell
            Rows(lngRowCounter).Hidden = blnHide ' all cols<>strCheck
        Next lngRowCounter

 

[Rhino999]

Hi everyone, I would like to thank everyone for trying to help me.
I want to let everyone know that I am very new to Visual Basic coding, so when I look at something, it's very difficult to decipher what exactly is going on.
Without step-by-step instructions of what each piece of code does, I have no idea about what that code is performing.

I'm afraid that we got a little away from what my initial intention was. No matter how I try to explain it to you, I just can't seem to get across what my intention was and I'm not able to understand what you are saying. The quote below illuminates that difference.
---------------------------------------------------------------------------
You said
"Hide the row, if any one of the columns G to P doesn't have a Y".

I said
"That is correct, if there are no "Y" 's on that row, in that range of columns, than the row gets hidden."
----------------------------------------------------------------------------


I would like to make sure is that everyone completely understands what I needed. If at any time a 'Y' sits on that row than I want to see that row. You could have a 'Y' in column G, or in column H or it could be in columns J or it could be in column P, or there could be 10 'Y's one 'Y' in each column G through P.
But if there are no 'Y's then the row get Hidden. I want to make sure everyone understand, because you're putting lot of time into this code. I'm very unsure about your code because it's quite expensive. So again, when I look at your code is difficult for me to figure out what is going on.

During this time I was able to get some code (below) that does exactly what I want. It seems to work fine and is much more simpler than what you wrote, so I'm afraid that the criteria for the code you developed, may not be what I was looking for.
I'm posting the code below so that there are no misunderstandings of what I was looking for.
If I sent you on a wild goose chase, I must apologize, and I would like to again thank you for your time.

This is the code that I have and it seems to work fine.
Range declarations
Dim rngRange As Range
Dim rngBlank As Range
Dim CellRow As Range
Application.ScreenUpdating = False
If ActiveSheet.Name = "Sheet1" Then

' Set check range ( You can change here )
Set rngRange = Range("G4999")

' For each blank cell
For Each CellRow In rngRange.Rows

' If that row is not hidden then hide
If WorksheetFunction.CountA(CellRow) = 0 And CellRow.EntireRow.Hidden = False Then
CellRow.EntireRow.Hidden = True
Else
CellRow.EntireRow.Hidden = False
End If

Next

End If

 

[Brianwarnock]

Ah! so the cells either contain Y or are empty, I had assumed that it could be any data, infact with this solution if anything is in one of the cells the row will not be hidden.

Part of the "complication" of our solutions was to provide the flexibility ro change the range and the search criteria without recoding, I thought that was a part of your requirement.

No harm done if you are happy with the solution, thanks for posting back.

Brian

 

[Rhino999]

Brian, again thanks for trying to help. I'm glad the code I posted finally allowed you to understand what I was initially looking for, even though my description could not.

I hope I did not waste too much of your time.

Thanks,
Rhino999

 

[Robert88]

Hi all,

I am new to using this code in Excel, done VBA in Access heaps but not Excel. Looking to hide blank rows as in your code but do not know how to use it.

Can someone help?