Copy all cells with a specific value to another column that skips spaces

Question

I have three columns: A, B and C:
Column A contains the names NAME1, NAME2, etc.
Column B contains only the values ​​"YES" or "NO".
Column C should contain names from column A that have the value "YES" in column B.

I can say that while the value is “YES” in column B, copy the value from column A to column C. Very simple:

C1=IF(B1="YES",A1,"") 

But this will include empty cells that I don't want. Therefore, I assume that I am looking for a way to copy all the names from column A with the value "YES" in column B and paste them into column C, skipping spaces.

I found a VBA project that colors all cells in a column with a specific value. I am not sure how to edit this in what I need. Here is the code I came up with so far.

QUESTIONS
1) Runtime Error '1004' User Defined or Object Error
2) Copy from column A
3) Check and remove duplicates from NewRange

EDIT 1 : added comment lines to code
EDIT 2 : change NewRange value from column A using offset (unchecked due to runtime error)
EDIT 3 : code to copy from one sheet, separate from code to paste into another sheet
EDIT 4 : added correction from user @abahgat
EDIT 5 : remove duplicates

 Sub RangeCopyPaste() Dim cell As Range Dim NewRange As Range Dim MyCount As Long MyCount = 1 '--> Loop through each cell in column B '--> Add each cell in column A with value "YES" in column B to NewRange 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 '--> Copy NewRange from inactive sheet into active sheet NewRange.Copy Destination:=activesheet.Range("C1") '--> Remove Duplicates activesheet.Range("C1:C30").RemoveDuplicates End Sub 

Answer 1

This will do the trick:

 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 

Answer 2

Solution without VBA:

column C contains formulas of the type:

 =COUNTIF(B$1:B1;"yes") 

column D contains formulas of the type:

 =INDEX(A:A;MATCH(ROW();C:C;0)) 

to skip errors:

 =IF(ISERROR(MATCH(ROW();C:C;0));"";INDEX(A:A;MATCH(ROW();C:C;0))) 

Answer 3

Just used the And condition on your If to avoid empty cells

• In C1 then put the copy =IF(AND(LEN(A1>0),B1="YES"),A1,NA()))
• Choose column C
  • Press f5
  • Special ... check Formulas , and then check "Errors" (see. Fig.).
  • Delete selected cells to leave you with a shorter list of desired names in column C