Excel VBA Last Empty Row VBA paste Array elements

[bbcdancer]

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]

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

 

[Tom Ogilvy]

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