Paste from Array to a Row
 

I found this code but it pastes in Column A. Can someone advise how I can
paste to the last row +1

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With

NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)

Tks

Paste from Array to a Row
 

That pastes in column A in the last row used in column A. Are you saying
that some other column needs to be used to determine the last used row, but
you want the data pasted in that row in column A?

You can use this function

Function LastRow(rng1 as Range) As Range
Dim rng As Range
Set rng = rng1.Parent.Cells.Find(What:="*", _
After:=rng1.parent.Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rng Is Nothing Then
Set LastRow = rng.EntireRow
Else
Set LastRow = Noting
End If
End Function


then in your code

With Worksheets("Data")
Set NextCell = .Cells(LastRow(.Cells(1,1)).Row,1)
End With

--
Regards,
Tom Ogilvy


"Dale" wrote:

I found this code but it pastes in Column A. Can someone advise how I can
paste to the last row +1

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With

NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)

Tks

Paste from Array to a Row
 

Sorry, yes finding the last row used in column A is fine what I was hoping to
be able to do is paste from array vBOD(1) to ARow() then vBOD(2) BRow() etc.

Tks Dale

"Tom Ogilvy" wrote:

That pastes in column A in the last row used in column A. Are you saying
that some other column needs to be used to determine the last used row, but
you want the data pasted in that row in column A?

You can use this function

Function LastRow(rng1 as Range) As Range
Dim rng As Range
Set rng = rng1.Parent.Cells.Find(What:="*", _
After:=rng1.parent.Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rng Is Nothing Then
Set LastRow = rng.EntireRow
Else
Set LastRow = Noting
End If
End Function


then in your code

With Worksheets("Data")
Set NextCell = .Cells(LastRow(.Cells(1,1)).Row,1)
End With

--
Regards,
Tom Ogilvy


"Dale" wrote:

I found this code but it pastes in Column A. Can someone advise how I can
paste to the last row +1

With Worksheets("Data")
Set NextCell = .Cells(.Rows.count, "A").End(xlUp).Offset(1, 0)
End With

NextCell.Resize(UBound(vBOD) - LBound(vBOD) + 1, 1).Value =
Application.Transpose(vBOD)

Tks