| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
better way to copy cells
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
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)) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
"Rick Rothstein" wrote in message ... 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) Thanks Rick I appreciate your help. Mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
Rick Rothstein was thinking very hard :
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) Still shining!<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
Rick Rothstein submitted this idea :
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) 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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
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) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
better way to copy cells
Rick Rothstein formulated on Saturday :
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) 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I copy big ranges of cells without drag or copy/paste? | Excel Discussion (Misc queries) | |||
| Copy and paste versus copy and insert copied cells | New Users to Excel | |||
| How can I copy the values of all selected cells to the same cells inanother Sheet | Excel Programming | |||
| Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
| Copy data into cells until changes trigger new copy | Excel Programming | |||
Hybrid Mode
