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: Fri, 22 Feb 2002 03:36:39 +0000
on 30 Jan 2002 19:38:19 +0100, I wrote:
>
> Date: Tue, 29 Jan 2002 00:23:09 -0600
>
> Subject: Re: End of column in Excel
>
> From: Jay Young <email@hidden>
>
> To: email@hidden
>
>
>
> Thank you Deivy, Kai & David,
(snip)
>
> The only problem is that
>
> if any rows are deleted before the script runs it will still grab the
>
> empty rows up to where the last item used to be. But I think that
>
> should be okay.
(snip)
>
After you've deleted or cleared any cells, simply save your work in Excel.
>
This should force an update - and result in the correct reference being
>
returned for 'LastCell'.
Jay - while working on another project, I just came up with a way to avoid
the need for such heavy manual labour! ;-)
You may recall that your final script went like this:
-------------------------------------
tell application "Microsoft Excel"
Activate
set CntOfRows to Row of (SpecialCells (Columns) Type xlLastCell)
--(grabs last row of longest column)
set FormulaR1C1 of Range "r1c2" to "=PROPER(RC[-1])" --(changing column
2 to the Proper Formula)
AutoFill Range "r1c2" Destination Range ("r1c2:r" & CntOfRows & "c2")
Type xlFillDefault
end tell --Excel
------------------------------------
To force an automatic update, simply insert the line:
UsedRange of ActiveSheet
...between lines 2 & 3 ('Activate' & 'set CntOfRows to...').
A modest refinement - but HTH!
Kai
--
**********************************
Kai Edwards Creative Resources
1 Compton Avenue Brighton UK
Telephone +44 (0)1273 326810
**********************************
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.