better way to copy cells

[mp]

I hacked this together based on recording a macro and revising
is there a better way?

'copy formulas in G, H, and I down one row
Sub CopyGHIDownOne(rowNumber As Long)

Range("G" & CStr(rowNumber) & ":I" & CStr(rowNumber)).Select
Selection.Copy
Range("G" & CStr(rowNumber + 1) & ":I" & CStr(rowNumber + 1)).Select
ActiveSheet.Paste

End Sub

thanks
mark

 

[GS]

mp pretended :

I hacked this together based on recording a macro and revising
is there a better way?

'copy formulas in G, H, and I down one row
Sub CopyGHIDownOne(rowNumber As Long)

Range("G" & CStr(rowNumber) & ":I" & CStr(rowNumber)).Select
Selection.Copy
Range("G" & CStr(rowNumber + 1) & ":I" & CStr(rowNumber + 1)).Select
ActiveSheet.Paste

End Sub

thanks
mark

Try this single line of code...

Range("G" &CStr(rowNumber).Resize(1, 3).Copy Range("G" & CSTR(rowNumber
+ 1))

 

[Rick Rothstein]

Try this single line of code...

Range("G" &CStr(rowNumber).Resize(1, 3).Copy Range("G" &
CSTR(rowNumber + 1))

How about this much shorter one-liner....

Cells(rowNumber, "G").Resize(2, 3).FillDown

and, if you don't mind using column numbers instead of letters, this even
shorter one still...

Cells(rowNumber, 7).Resize(2, 3).FillDown

Rick Rothstein (MVP - Excel)

 

[mp]

How about this much shorter one-liner....

Cells(rowNumber, "G").Resize(2, 3).FillDown

and, if you don't mind using column numbers instead of letters, this even
shorter one still...

Cells(rowNumber, 7).Resize(2, 3).FillDown

Rick Rothstein (MVP - Excel)

Thanks Rick
I appreciate your help.
Mark

 

[GS]

Rick Rothstein was thinking very hard :

How about this much shorter one-liner....

Cells(rowNumber, "G").Resize(2, 3).FillDown

and, if you don't mind using column numbers instead of letters, this even
shorter one still...

Cells(rowNumber, 7).Resize(2, 3).FillDown

Rick Rothstein (MVP - Excel)

Still shining!<g>

 

[GS]

Rick Rothstein submitted this idea :

How about this much shorter one-liner....

Cells(rowNumber, "G").Resize(2, 3).FillDown

and, if you don't mind using column numbers instead of letters, this even
shorter one still...

Cells(rowNumber, 7).Resize(2, 3).FillDown

Rick Rothstein (MVP - Excel)

Not trying to dim your shine (which is not possible to do<g>), Rick,
but it just occured to me that your solution will only work if the
cells are contiguous. Mine will work whether they're contiguous or not
contiguous!

 

[Rick Rothstein]

but it just occured to me that your solution will only work if the cells

are contiguous. Mine will work whether they're contiguous or not
contiguous!

Can you show me the VB statement you have in mind for your non-contiguous
case?

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein formulated on Saturday :

Can you show me the VB statement you have in mind for your non-contiguous
case?

Rick Rothstein (MVP - Excel)

Yes. In my 1st reply it's the line that uses the destination arg of the
Copy method. Though, in this case, the destination address is
hard-coded. Under normal usage this would not be the case as it would
be retrieved during runtime via some means or another.

So then, the line could be written something like:

rngSource.Copy rngTarget

...which could be anywhere on any sheet in any open workbook.

 

[Rick Rothstein]

So then, the line could be written something like:

rngSource.Copy rngTarget

..which could be anywhere on any sheet in any open workbook.

Okay, there are two problems with doing that. First, if rngSource is
non-contiguous, rngTarget cannot be non-contiguous... so neither of our
methods could be used for the OP's request if his initial range is
non-contiguous. But, second, the bigger problem with your approach (at least
with respect to the OP's question) is that if rngSource is non-contiguous,
what gets copied to rngTarget is the **values** in rngSource, **not** the
formulas in those cells... the formulas are copied with the Copy method only
when the cells being copied are contiguous.

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein expressed precisely :

Okay, there are two problems with doing that. First, if rngSource is
non-contiguous, rngTarget cannot be non-contiguous... so neither of our
methods could be used for the OP's request if his initial range is
non-contiguous. But, second, the bigger problem with your approach (at least
with respect to the OP's question) is that if rngSource is non-contiguous,
what gets copied to rngTarget is the **values** in rngSource, **not** the
formulas in those cells... the formulas are copied with the Copy method only
when the cells being copied are contiguous.

Rick Rothstein (MVP - Excel)

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.

Your solution requires that the source be contiguous cells AND the
destination be identical as well as contiguous.

My solution requires only the source be contiguous. The destination
could be a single cell (allbeit that adjacent cells will be
overwritten).

If the source cells are not contiguous then we would be dealing with
areas OR an array, which <IMO> needs to be handled differently.

 

[Rick Rothstein]

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)

 

[GS]

Rick,
These are all good points! I fully agree...

Also, I wasn't arguing about one being better than the other either. I
was acknowledging that I felt yours was better in this context.

As always, I appreciate you sharing your knowledge and expertise.