0

I wanted a quick simple way to copy cell values to another sheet using SpecialCells in Excel as opposed to looping

My VBA code is as below:

Sub copyMissingData()
    Worksheets("Source").Range("Z4:Z2000").SpecialCells(xlCellTypeConstants).Copy Worksheets("Destination").Range("missing_qbc")
End Sub

My source data Z4:Z20000 has formulas that returns a value (texts/numbers/fraction etc) or blank "". I want the copy to ignore the blanks, but copy any other value returned

The VBA code above using SpecialCells(xlCellTypeConstants) doesn't work because of the formula in the source range.

My question: Is there a straightforward way I can use range.specialcells to copy my data from a worksheet to another bearing in mind that source cells contain formulas and the formulas may produce empty string cells which will need to be skipped

yinka
  • 134
  • 1
  • 5
  • 15
  • This post is a possible duplicate of [Excel VBA Copy Range and Paste Values in another Sheet's specific Range](http://stackoverflow.com/questions/21648122/excel-vba-copy-range-and-paste-values-in-another-sheets-specific-range) (since you have the range already correctly determined the range with SpecialCells). – Ralph Oct 14 '16 at 10:13
  • @ralph this is not a duplicate of that in this case - not about copy-paste special, but range.specialcells. – vacip Oct 14 '16 at 10:16
  • @vacip Depends whether I understand the question correctly or you did. I am reading from this post that the range is correctly determined. Yet, formulas are copied over and yinka wants to copy values only. You are reading that the range is not correctly determined. But the copy / paste works correctly. I just read through the post again and it I am still not sure if you got the OP right or if I understood the OP correctly. Yet, it seems that Ron Rosenfeld is sharing my thought process. I guess the OP will have to clarify. – Ralph Oct 14 '16 at 10:20

2 Answers2

2

If you have formulas, why are you trying to select the constants?

Use this:

Worksheets("Source").Range("Z4:Z2000").SpecialCells(xlCellTypeFormulas, 23).Copy
Worksheets("Destination").Range("missing_qbc").pastespecial(xlPasteValues)

The 23 means "Numbers, Texts, Logicals and Errors".

Doing the copy and paste separately ensure blanks are skipped (if that's what you mean by "ignore").

Paste values makes sure only the values get pasted, not the formulas themselves.

Please note that if you have a formula in a cell, it is not blank. Even if the formula produces an empty string value as a result, the cell itself is not empty! In htat case, you need to do a copy-paste values in place before you do anything else - and even then Excel sometimes doesn't consider blank cells blank. If this is the case, you need to iterate (loop) through the cells, and copy them one-by-one.

vacip
  • 5,246
  • 2
  • 26
  • 54
  • To just paste the values, use the `.Copy` method on that selection; then `.PasteSpecial(xlPasteValues)` – Ron Rosenfeld Oct 14 '16 at 10:59
  • @vacip This copied it but didn't ignore blanks – yinka Oct 14 '16 at 13:16
  • @yinka It is very very hard to understand what you are asking for... And I have a feeling you only provide 10% of the problem, so my solution will bump into another, unmentioned problem in your worksheet. Anyway, see my updated answer. – vacip Oct 14 '16 at 13:55
  • @vacip I edited my question, but from your latest edit, it looks like I'll have to loop. Thanks – yinka Oct 14 '16 at 14:17
2

The easiest way I can think of is to remove the blanks after copying all:

Set rngFrom = [Source!Z4:Z2000]
Set rngTo = [Destination!missing_qbc].Resize(rngFrom.Rows.Count, 1)
rngTo.Value = rngFrom.Value
rngTo.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

The more complicated way is with array formula, but doesn't need VBA.

Slai
  • 22,144
  • 5
  • 45
  • 53