Dim rng as Range
Dim s as Long, t as Long
Dim cell as Range
set rng = Range("Q5:R2000").SpecialCells(xlFormulas,1)
for each cell in rng
if cell.Column = 17 then
s = s + 1
cells(s,"S").Value = cell.Value
else
t = t + 1
cells(t,"T").Value = cell.value
end if
Next

If I know more about what is in the source columns, there might be quicker
ways. For example, if the specialcells will pick up every non empty cell,
then you could equate the areas then select with specialcells for blanks and
delete those cells.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Is it possible to copy a column (or range) of data, remove all the
blank cells and paste the results into another column (or range)
without using the clipboard?

So far I am using the following, but I am trying to avoid the clipboard
to save time.

Application.ScreenUpdating = False
Range("S5:S2000,T5:T2000").ClearContents
Range("Q5:Q2000,R5:R2000").SpecialCells(xlCellType Formulas, 1).Copy
Range("S5:T5").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Calculate
Application.ScreenUpdating = True


thanks in advance