Skipping empty cells for a Range object?

  • Thread starter Thread starter Zilla
  • Start date Start date
Z

Zilla

In VBA..

Is it possible to set a Range object to skip empty cels? IOW, if I
copy A1:A10, and A5 is empty, and transpose them on B20, I want to
skip A5; I did SkipBlanks:=True and it still pasted the blank cells.
Incidentally, doing it via Paste Special wizard, and checking skip
blank cells produces the same result.
 
Straight copy (no trasnpose):

Sub skip_copy()
For i = 1 To 10
v = Cells(i, 1).Value
If IsEmpty(v) Then
Else
Cells(i, 1).Copy Cells(i + 19, 2)
End If
Next
End Sub


With transpose:

Sub skip_copy()
For i = 1 To 10
v = Cells(i, 1).Value
If IsEmpty(v) Then
Else
Cells(i, 1).Copy Cells(20, i+1)
End If
Next
End Sub
 
Back
Top