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

Leave a Reply

Your email address will not be published. Required fields are marked *