Re: End of column in Excel
Re: End of column in Excel
- Subject: Re: End of column in Excel
- From: Kai Edwards <email@hidden>
- Date: Tue, 29 Jan 2002 04:26:10 +0100
>
Date: Mon, 28 Jan 2002 17:32:52 -0600
>
Subject: End of column in Excel
>
From: Jay Young <email@hidden>
>
To: email@hidden
>
>
I'm trying to select all the text from a column in Excel without
>
selecting the empty cells at the end, but can't figure it out. My plans
>
are to create a formula in column 2 next to the first cell in column 1
>
and then drag that formula down using autofill to the last line of text
>
in column 1. I've got most of this figured out (thanks to Excel's
>
recording capabilities) but getting the autofill to stop at the last
>
line of text of column 1 is what I can't figure out. This is what I've
>
come up with so far for finding the last cell in column 1:
>
>
tell app "MicroSoft Excel"
>
get LastCell (column 1) direction xlDown
>
end tell
It's some time since I did any scripting in Excel but - if you don't get
anything better - you could try something like this:
tell application "Microsoft Excel"
set lastRow to Row of (LastCell (last Row of Column 1) Direction xlUp)
FillDown Range ("R1C2:R" & lastRow & "C2")
end tell
One word of warning: if you enter a formula referring to the values in
column 1, don't expect blanks in the neighbouring column 2 cells. You'll
probably get zeros - unless you enter something like this in row 1 of column
2:
=IF(ISBLANK(A1),"", xyz)
- where xyz is the formula you would normally have entered in column 2.
Since I haven't had time to look at the problem in any depth, this may all
be regarded as a bit of a workaround. However, you could well get a more
elegant solution from one of the list gurus! ;)
HTH
Kai
--
**********************************
Kai Edwards Creative Resources
1 Compton Avenue Brighton UK
Telephone +44 (0)1273 326810
**********************************