Using xlDown as a function?

J

John Sutton

I'd like to append data to the bottom of an existing spreadsheet, I
have a macro that will import the data, I just need to know where to
paste it. Can I use something like (((Selection.End(xlDown))+1).Select
to select the row below the bottom?

John
KBS
 
D

Dave Peterson

I like to pick a column that always has data and do this:

dim DestCell as range
with worksheets("sheet1")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

somethingthatyouneed.copy _
destination:=destcell
 
M

Michael Bednarek

I like to pick a column that always has data and do this:

dim DestCell as range
with worksheets("sheet1")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

somethingthatyouneed.copy _
destination:=destcell
Click to expand...

If the presence of data cannot be guaranteed in that column,
this should work:

Function FirstEmptyCell(strCol As String) As Range
With ActiveSheet
Set FirstEmptyCell = .Cells(.Rows.Count, strCol).End(xlUp)
If Len(FirstEmptyCell.Value) <> 0 Then Set FirstEmptyCell = FirstEmptyCell.Offset(1, 0)
End With
End Function

and subsequently:

somethingthatyouneed.Copy Destination:=FirstEmptyCell("A")
 
D

Dave Peterson

It might be better to check the actual emptiness of that cell--not just if its
value was a 0 length string.

dim DestCell as range
with worksheets("sheet1")
set destcell = .cells(.rows.count,"A").end(xlup)
if isempty(destcell) then
'keep it there
else
set destcell = destcell.offset(1,0)
end if
end with

But this still ignores the possibility that the last row in the column (a65536)
has something in it.

somethingthatyouneed.copy _
destination:=destcell
 
M

Michael Bednarek

It might be better to check the actual emptiness of that cell--not just if its
value was a 0 length string.

dim DestCell as range
with worksheets("sheet1")
set destcell = .cells(.rows.count,"A").end(xlup)
if isempty(destcell) then
'keep it there
else
set destcell = destcell.offset(1,0)
end if
end with
Click to expand...

Ahh ... I looked at IsEmpty but interpreted the explanation in Help:
"Returns a Boolean value indicating whether a variable has been
initialized."
to mean that it cannot be applied to ranges. Trying that would have
shown that indeed it does. Thanks.
But this still ignores the possibility that the last row in the column (a65536)
has something in it.
Click to expand...
[snip]

Of course, but the finer points of error/boundary checking are
customarily left as an exercise for the reader. Or as an unexpected
run-time error message for the user.
 
Top