Using xlDown as a function?

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

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



John Sutton wrote:
>
> 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

--

Dave Peterson

On Tue, 07 Feb 2006 14:32:58 -0600, Dave Peterson wrote in
microsoft.public.excel:

>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

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")

>John Sutton wrote:
>>
>> 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?

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

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

Michael Bednarek wrote:
>
> On Tue, 07 Feb 2006 14:32:58 -0600, Dave Peterson wrote in
> microsoft.public.excel:
>
> >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
>
> 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")
>
> >John Sutton wrote:
> >>
> >> 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?
>
> --
> Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

--

Dave Peterson

On Wed, 08 Feb 2006 06:27:04 -0600, Dave Peterson wrote in
microsoft.public.excel:

>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

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.
[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.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"