How do I copy cells containing value only using VBA?

user_15596129 0
Viewed 73

I have a range ("B56:J62") which contains some empty rows.
I tried to copy using the command below but it is copying the empty rows too.
How can I copy only the rows with value within that range.

Sub Prod_Drilling_DS()
'PURPOSE: Paste to D&B Sheet
   
'Copy A Range of Data
Worksheets("Production Sheet").Range("B55:J62").Copy

'PasteSpecial Values Only
Worksheets("Production Drilling").Range("B65500").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

'Clear Clipboard (removes "marching ants" around your original data set)
Application.CutCopyMode = False
  
End Sub
vba excel
1 Answers

The solution

You can qualify the cells you want to copy with SpecialCells(xlCellTypeConstants), like this:

Sub Prod_Drilling_DS() 
'PURPOSE: Paste to D&B Sheet

'Copy A Range of Data (copy only cells containing data)
Worksheets("Production Sheet").Range("B55:J62").SpecialCells(xlCellTypeConstants).Copy

'PasteSpecial Values Only 
Worksheets("Production Drilling").Range("B65500").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

'Clear Clipboard (removes "marching ants" around your original data set) 
Application.CutCopyMode = False

End Sub

The reason

The relevant part of the code was in which cells to copy. (In this case, you only wanted to copy cells that contain values).

You had also specified a special type of paste, pasting "values".

  • This method of pasting converts all content (formulas and values) to values.
  • That is, any formulas are replaced by their results which are then 'hard coded' in when pasted.
  • Although it specifies how to paste the copied items, it does not filter which items that are to be pasted.

In order to filter what is pasted, in this instance you actually needed to filter what is copied. The above solution achieves this.

Extending the solution

If you want to include cells with either values or formulas, you would need to copy cells of type xlCellTypeConstants and also of type xlCellTypeFormulas. This answer can be modified to achieve that if needed.

user_141529890
Related