# Copy to a mid column range Row 10 - Row 20

L

#### L. Howard

Trying to convet the commented out line to copy to row 10 and offset from there on down to row 20.

Assumes there is data in the rows above 10 and below 20, so my availsble range to copy is between 10 to 20.

The "FERow" part of a previous code I failed to properly add instructive comments about and thus I need some redirection.

Thanks,
Howard

Sub CopyToRange_ArrOut()
Dim arrOut As Variant
arrOut = Range("C12:F12")

' FERow = WorksheetFunction.Max(10, .Cells(20, 1).End(xlUp).Offset(1, 0).Row)
' .Cells(FERow, 1).Resize(columnsize:=4) = arrOut

Cells(Rows.Count, "C").End(xlUp)(2) _
.Resize(columnsize:=Range("C2:F2").Columns.Count) = arrOut

End Sub

C

#### Claus Busch

Hi Howard,

Am Sun, 27 Jul 2014 23:17:06 -0700 (PDT) schrieb L. Howard:
Trying to convet the commented out line to copy to row 10 and offset from there on down to row 20.

Assumes there is data in the rows above 10 and below 20, so my availsble range to copy is between 10 to 20.

what range do you want to copy? Where should the data be pasted?
Do you want to copy the C12:F12 to column Q? Then try:

Sub CopyToRange_ArrOut()
Dim arrOut As Variant
Dim FERow As Long

With Sheets("Sheet1")
arrOut = .Range("C12:F12")

FERow = WorksheetFunction.Max(10, .Cells(20,
"Q").End(xlUp).Offset(1, 0).Row)
If IsEmpty(.Cells(FERow, "Q")) Then
.Cells(FERow, "Q").Resize(columnsize:=4) = arrOut
End If
End With
End Sub

Regards
Claus B.

L

#### L. Howard

Hi Claus,
what range do you want to copy? Where should the data be pasted?

Do you want to copy the C12:F12 to column Q? Then try:

Sorry Claus, should have given some ranges.

I did not have any specific copy need, just wanted a working example of the mid range column target.

As I read the code, It looks to see if Q10 is blank and if it is blank then copy to Q10 otherwise offset from previous copy.

Works great.

Thanks a bunch.

Howard

C

#### Claus Busch

Hi Howard,

Am Mon, 28 Jul 2014 00:18:13 -0700 (PDT) schrieb L. Howard:
I did not have any specific copy need, just wanted a working example of the mid range column target.

As I read the code, It looks to see if Q10 is blank and if it is blank then copy to Q10 otherwise offset from previous copy.

the IF-statement is superfluous:

Sub CopyToRange_ArrOut()
Dim arrOut As Variant
Dim FERow As Long

With Sheets("Sheet1")
arrOut = .Range("C12:F12")

FERow = WorksheetFunction.Max(10, .Cells(20,
"Q").End(xlUp).Offset(1, 0).Row)
.Cells(FERow, "Q").Resize(columnsize:=4) = arrOut

End With
End Sub

Regards
Claus B.

L

#### L. Howard

Hi Howard,

Am Mon, 28 Jul 2014 00:18:13 -0700 (PDT) schrieb L. Howard:

the IF-statement is superfluous:

Sub CopyToRange_ArrOut()

Dim arrOut As Variant

Dim FERow As Long

With Sheets("Sheet1")

arrOut = .Range("C12:F12")

FERow = WorksheetFunction.Max(10, .Cells(20,

"Q").End(xlUp).Offset(1, 0).Row)

.Cells(FERow, "Q").Resize(columnsize:=4) = arrOut

End With

End Sub

Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Great.

Thanks again, Claus.

Howard