I have columns A B C, Column A contains names, Column B contains value “YES” or “NO”, Column C is suppose to contain names from column A that have value “YES” in column B.

This formula include blanks cells which I don’t want: C1=IF(B1=”YES”,A1,””)

The online answer below copied column B data to column C, what I need is data from column A to column C. Many thanks in advance.

Sub RangeCopyPaste()

Dim cell As Range

Dim NewRange As Range

Dim MyCount As Long

MyCount = 1

For Each cell In Worksheets(“Sheet1”).Range(“B1:B30”)

If cell.Value = “YES” Then

If MyCount = 1 Then Set NewRange = cell.Offset(0,-1)

Set NewRange = Application.Union(NewRange, cell.Offset(0,-1))

MyCount = MyCount + 1

End If

Next cell

NewRange.Copy Destination:=activesheet.Range(“D1”)

End Sub

By: suohamchiang