Producing a list if a column has a value Y Solved/Closed

Question

Hello, 

I wish to produce a list only when one of the columns has a Y in it.

For example; 

     A  B  
1   Y  Bread
2       Milk
3   Y  Butter

On a separate worksheet I would like to produce a list that looks at the value Y in column A and prints the text in column B. The problem is I don't want any blank rows!? help anyone? 

The new worksheet just needs to show

     A    
1   Bread
2   Butter


Configuration: Windows XP / Firefox 3.6.9

TrowaD · Accepted Answer

Hi Russ,

Try this code:
Sub test()
Set MyRng = Range("A1:A5")
For Each cell In MyRng
If cell.Value = "Y" Then
    cell.Offset(0, 1).Copy _
    Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next
End Sub
Take note on the following:
2nd line of code: Range("A1:A5"). This is the range where a Y might be placed. Change the range according to your situation.
6th line of code: Sheets("Sheet2") this is the name of you second or destination sheet. Change the name according to your situation.

Does this suit your needs?

Best regards,
Trowa

Producing a list if a column has a value Y

1 response

Similar discussions

Subscribe To Our Newsletter!