Producing a list if a column has a value Y

Solved/Closed
Report
-
 Russ -
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


1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
2
Hi Trowa - Works great, I need to tweak it a bit for my actual scenario but should be able to fathom it thanks to your reply - thanks again, much appreciated!!
0