I agree. In the context of this OP's use, your solution is
(IMO) better than mine. My point was about the flexibility
of the solution in other contexts.
Actually, I wasn't arguing for one method being better than the other...
basically (no pun intended), I believe if one's code does what one
ultimately wants, then it is good code. The question of efficiency comes
into play only if that code is taking much too long to execute, at which
point alternate coding should be sought.
Your solution requires that the source be contiguous cells
AND the destination be identical as well as contiguous.
That is true. I suspect that underneath it all, FindDown is just using the
underlying code for the Copy method to copy contiguous cells into contiguous
cells underneath the source (so it craps out with a non-contiguous source in
the same way Copy does).
My solution requires only the source be contiguous. The
destination could be a single cell (allbeit that adjacent cells
will be overwritten).
Except don't lose sight of the fact that for a non-contiguous source, only
the values from the cells in that source get copied... the formulas are lost
to the target range. That is a biggie that tends to get lost sight of when
using the Copy method... formulas only copy across when the source is
contiguous, otherwise you get values only.
If the source cells are not contiguous then we would be
dealing with areas OR an array, which <IMO> needs to be
handled differently.
Correct. Here one would simply iterate each Area in the ranges Areas
property and copy that to its new destination (that way, formulas would be
copied). Although if the destination will always be empty, it might be
faster to copy the contiguous range that includes all the areas of interest
and then delete the ranges that are not wanted.
Rick Rothstein (MVP - Excel)