Macro: Find last column with data

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
rapco
Posts: 1
Joined: Fri Feb 08, 2008 6:37 am
Location: Panama

Macro: Find last column with data

Post by rapco »

Hi,

In Excel I made copy a rudimentary macro which detects the last row with data and then I use that replaces some values, however I am not able to get it to work in Calc.

I am very new in macros on both Excel and Calc so I may ask some dumb clarification questions

Sub Macro1()
LastRow = Range("A65536").End(xlUp).Row
ActiveSheet.Range("A2:A" & LastRow) = "TEXTVALUE"
ActiveSheet.Range("AB2:AB" & LastRow) = "ANYNUMBER"
End Sub

Thanks,
User avatar
uros
Volunteer
Posts: 30
Joined: Sun Dec 02, 2007 10:26 pm
Location: Slovenia

Re: Macro: Find last column with data

Post by uros »

Hi rapco!
This code finds range wiht data and in there find and replace some text. So it's probably not exactly what you want. Finding the occupied range will work for you, I'm not so sure how do you want to search and replace data. Search in column A, then replace text in column AB? Hope it helps somehow...

Code: Select all

Sub ReplaceInUsedRange
	oDocument = ThisComponent
	oSheets = oDocument.Sheets
	oSheet = oSheets.getByIndex(0)
	oCellCursor = oSheet.createCursor()

	oCellCursor.gotoStartOfUsedArea( False )
	nFirstRow = oCellCursor.getRangeAddress().StartRow
	nFirstCol = oCellCursor.getRangeAddress().StartColumn

	oCellCursor.gotoEndOfUsedArea( False )
	nLastRow = oCellCursor.getRangeAddress().EndRow
	nLastCol = oCellCursor.getRangeAddress().EndColumn

	oRange = oSheet.getCellRangeByPosition(nFirstCol,nFirstRow,nLastCol,nLastRow)
	vDescriptor = oRange.createReplaceDescriptor()
	With vDescriptor
		.SearchString = "SearchString"
		.ReplaceString = "ReplaceString"
		.SearchWords = False
		.SearchCaseSensitive = False
	End With
	nReplaced = oRange.replaceAll( vDescriptor )
	MsgBox "Replaced " & nReplaced & " times."
End Sub
Uros
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Macro: Find last column with data

Post by TerryE »

Rapco, I'm afraid that there is no quick way the macro programming. You need to get to grips with the basic language, and the link to the wiki documentation is at the top of this forum. There is also a slightly older PDF version called the Staroffice 8 Programmer's guide, and you will also need to be familiar with the API as documented in the SDK, which is also downloadable from the OOo site. At 20,000 feet OOo Basic and VBA are broadly similar, however there are many differences between the Excel DCOM API and the Calc UNO API, though Noel Power from Novell is leading a project to provide VBA compatibility within calc. if you search of the OOo wiki, then you will find a reference to this if you search it for VBA.

However I recommend that you get to grips with the native UNO API. One of the key differences between Excel and Calc is that Calc uses a concept called a cursor to navigate around a sheet. I could give you the code fragment to do this but instead why not Google "openoffice calc cursor gotoend" and work it out yourself :-)

PS. B****r, UROS beat me to it with the simple answer :-)
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro: Find last column with data

Post by Villeroy »

Code: Select all

ActiveSheet.Range("A2:A" & LastRow) = "TEXTVALUE"
Want to put something into A2:Axxx?
Jump to A1: Ctrl+Home
One down: down arrow
Expand from A2 to last used cell: Ctrl+Shift+End
Keep range selection and goto first column: Shift+Home
Now A2:Axxx is selected, type the value and hit Alt+Enter

Code: Select all

ActiveSheet.Range("AB2:AB" & LastRow) = "ANYNUMBER"
Want to put something into AB2:ABxxx?
Jump to last used cell: Ctrl+End
Let's say we are in BVxxx, which is shown in the name box (left of the formula bar.
Change "BVxxx" to "AB2:ABxxx" [Enter]
Now AB2:ABxxx is selected, type the value and hit Alt+Enter
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply