Excel VBA Last Empty Row VBA paste Array elements

I have a array and the array size is 1 to 5.

So far my macro pastes values into Column A, worksheet Sheet1 and Cells
A2:A6

- What I would like is a amendment to this macro so it identifies the
last
empty cell in column A and then paste the additional array elements in
that column A list.

- Because the array data elements is always updated and exported in MS
Excel, the last
empty cell in column A is not fixed.

Is this doable and many thanks in-advance.


Public Sub AddtoWorksheet()

Dim myArray(1 To 5) As String


myArray(1) = "Value1"
myArray(2) = "Value2"
myArray(3) = "Value3"
myArray(4) = "Value4"
myArray(5) = "Value5"


Worksheets("Sheet1").Select

range("A2:A" & UBound(myArray) + 1) =
WorksheetFunction.Transpose(myArray)


End Sub

Last used cell and then pastes it one cell below???

dim NextCell as range
with activesheet
set nextcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with


Kind of like:

Option Explicit
Public Sub AddtoWorksheet()

Dim NextCell As Range
Dim myArray(1 To 5) As String

myArray(1) = "Value1"
myArray(2) = "Value2"
myArray(3) = "Value3"
myArray(4) = "Value4"
myArray(5) = "Value5"

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

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

End Sub



[email protected] wrote:
>
> I have a array and the array size is 1 to 5.
>
> So far my macro pastes values into Column A, worksheet Sheet1 and Cells
> A2:A6
>
> - What I would like is a amendment to this macro so it identifies the
> last
> empty cell in column A and then paste the additional array elements in
> that column A list.
>
> - Because the array data elements is always updated and exported in MS
> Excel, the last
> empty cell in column A is not fixed.
>
> Is this doable and many thanks in-advance.
>
> Public Sub AddtoWorksheet()
>
> Dim myArray(1 To 5) As String
>
> myArray(1) = "Value1"
> myArray(2) = "Value2"
> myArray(3) = "Value3"
> myArray(4) = "Value4"
> myArray(5) = "Value5"
>
> Worksheets("Sheet1").Select
>
> range("A2:A" & UBound(myArray) + 1) =
> WorksheetFunction.Transpose(myArray)
>
>
> End Sub

--

Dave Peterson

Public Sub AddtoWorksheet()

Dim myArray(1 To 5) As String


myArray(1) = "Value1"
myArray(2) = "Value2"
myArray(3) = "Value3"
myArray(4) = "Value4"
myArray(5) = "Value5"


Worksheets("Sheet1").Select
lastrow = cells(rows.count,1).End(xlup)(2).row
range("A" & lastrow & ":A" & UBound(myArray) + 1) =
WorksheetFunction.Transpose(myArray)
End Sub


--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> I have a array and the array size is 1 to 5.
>
> So far my macro pastes values into Column A, worksheet Sheet1 and Cells
> A2:A6
>
> - What I would like is a amendment to this macro so it identifies the
> last
> empty cell in column A and then paste the additional array elements in
> that column A list.
>
> - Because the array data elements is always updated and exported in MS
> Excel, the last
> empty cell in column A is not fixed.
>
> Is this doable and many thanks in-advance.
>
>
> Public Sub AddtoWorksheet()
>
> Dim myArray(1 To 5) As String
>
>
> myArray(1) = "Value1"
> myArray(2) = "Value2"
> myArray(3) = "Value3"
> myArray(4) = "Value4"
> myArray(5) = "Value5"
>
>
> Worksheets("Sheet1").Select
>
> range("A2:A" & UBound(myArray) + 1) =
> WorksheetFunction.Transpose(myArray)
>
>
> End Sub
>