Andy,
Impressive stuff ! I will certainly give it a try. Not least I will
learn something about macros ! Many thanks.
Mike
Andy Lewis wrote:
Mike Couling wrote:
[snip other suggestions]
- I very often copy & paste columns of data to text files for
external manipulation. A column copy puts all 64K rows on the
clipboard regardless of how many are used. Could it be changed to
only copy the used rows as in Excel?
I agree with you on this - I found the same. Perhaps you'd like to
try this macro (below), which does what you want. (It copies the
smallest rectangle which encloses all the cells from within the
selected cell range(s) which actually contain data.)
To install it, open a calc document, go to Tools-Macros-Organise
macros-Openoffice.org Basic...
Then Click on Organiser... Click the + next to My macros and Select
"Standard"
Then Click New and give the new module a name.
Then click Edit. Delete the empty macro which appears in the basic
editor, and paste in the macro below. Hit Save. Close the basic
editor window.
Now to make the macro easily accessible, I bind it to
"Shift-Control-C". (Easy to remember - a kind of special
"control-C"). To do this, back in the calc document, go to
Tools-Customise and select the keyboard tab. Down at the bottom left,
select category "OpenOffice Basic", then "User", then "Standard", then
whatever you called the module (ridiculously small dialog here - you
have to scroll horizontally to see anything). In the box to the
right, highlight the function "CopyUsed". Now, up the top, scroll
down to select Control+Shift+C (or whatever you choose) and click
Modify. That's it.
[You can make a toolbar button and/or menu item for it as well, or
instead, if you prefer.]
Hope it works for you.
Andy
Now for the macro:
Sub CopyUsed()
Selection = ThisComponent.getCurrentSelection()
'Check whether multiple ranges selected
if Selection.supportsService("com.sun.star.sheet.SheetCellRanges") then
SelectionRanges = Selection
Selection = SelectionRanges.getByIndex(0)
else
SelectionRanges =
ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
SelectionRanges.insertByName("", Selection)
end if
'Create list of cells within the selection which are used, and check
if empty
UsedCells = SelectionRanges.cells.createEnumeration()
If not UsedCells.hasMoreElements() then exit sub
'Find top left and bottom right of cells actually used
MinCol=65535:MinRow=255:MaxCol=0:MaxRow=0
do while UsedCells.hasMoreElements()
ThisCell = UsedCells.nextelement().CellAddress
if ThisCell.Column<minCol then minCol=ThisCell.Column
if ThisCell.Row<minRow then minRow=ThisCell.Row
if ThisCell.Column>maxCol then maxCol=ThisCell.Column
if ThisCell.Row>maxRow then maxRow=ThisCell.Row
loop
'Create new selection based on above
ThisSheet = Selection.getSpreadsheet()
RequiredRange =
ThisSheet.getCellRangeByPosition(minCol,minRow,maxCol,maxRow)
ThisComponent.getCurrentController().Select(RequiredRange)
'Copy this new selection
docframe = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(docframe, ".uno:Copy", "", 0, Array())
End Sub
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
