Copy all cells with certain value into another column skipping blanks

Question

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

I can say that as long as the value is "YES" in column B, copy the value from column A to column C. Very simple with:

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

But this will include blank cells, which I don't want to. So I guess I am looking for a way to copy all the names from column A with value "YES" in column B and paste them into column C skipping the blanks.

I did find a VBA project that colors all the cells within a column with a certain value. I am not sure how to edit this into what I need. Here is the code I came up with so far.

ISSUES
1) ~~Runtime Error '1004' Application-defined or Object-defined error~~
2) ~~Copying from Column A~~
3) ~~Check and Remove Duplicates from NewRange~~

EDIT 1: Added comment rows into the code
EDIT 2: Change NewRange to be made from column A with Offset (untested due to runtime error)
EDIT 3: Code for copying form one sheet separated from code for pasting 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

score 6 · Answer 1 · edited Mar 21 '19 at 09:33

Solution without VBA:

column C contains formulas like:

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

increase number in column C if this row has "yes" value in column B.
This value will by used in next step.

column D contains formulas like:

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

take value from:
table: an entire A row
row number: calculated by match function: find first occurance of row number (row number where we will place the value) in entire C column. 0 meens that we looking for exactly this number not an clossest.

to skip errors:

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

easier can be writen:

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

and this means: write the value from rule if this value is not an error or write empty string if the rule is an error

Could someone please explain this. It's incredibly vague and unclear right now unless you know what you're doing. Thanks :) — theforestecologist, Jun 27 '17 at 15:40

brettdj · Answer 2 · 2012-11-20T10:16:37.877

2

Just used a Andcondition on your If to avoid the empty cells

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

enter image description here

edited Nov 20 '12 at 10:16

answered Nov 20 '12 at 09:40

brettdj

54,857
16
114
177

Thank you for the reply but it doesn't seem to work. It does exactly the same as what I described before. The rows where the Name gets a value "NO" contains a blank in column C. But column C should not have any blanks, it should always be equal or smaller in length than the other two columns. Column C should be a copy of all the "YES"-names without blanks (so there might be names on the 'wrong' row). I hope I explain myself correctly. – Ampi Severe Nov 20 '12 at 10:06
@AmpiSevere that makes sense - see above – brettdj Nov 20 '12 at 10:16
1

Thanks, this does work perfectly, but (sorry :-)) I have to do this for 8 different 'B' columns and I would like to be able to edit the "YES" and "NO" values. Is it possible to do this automatically? Meanwhile I will use this method so thank you! – Ampi Severe Nov 20 '12 at 10:40
I realized that when I only select and copy certain cells from a column and then paste them, (logically) only the selected and copied cells are pasted, and thus automatically skipping the blanks. The only problem I'm left with now is (see newly aded VBA code above) how to copy only the NewRange and paste it in a certain range in a worksheet of my choosing. Any ideas? – Ampi Severe Nov 21 '12 at 08:35

score 1 · Accepted Answer · answered Nov 23 '12 at 14:22

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

Copy all cells with certain value into another column skipping blanks

3 Answers3

Linked