Macro: Find last column with data

Keyboard macros or custom scripts

Macro: Find last column with data

Postby rapco » Fri Feb 08, 2008 7:01 am


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

Posts: 1
Joined: Fri Feb 08, 2008 6:37 am
Location: Panama

Re: Macro: Find last column with data

Postby uros » Fri Feb 08, 2008 3:21 pm

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   Expand viewCollapse view
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
User avatar
Posts: 30
Joined: Sun Dec 02, 2007 10:26 pm
Location: Slovenia

Re: Macro: Find last column with data

Postby TerryE » Fri Feb 08, 2008 3:34 pm

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.
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Macro: Find last column with data

Postby Villeroy » Fri Feb 08, 2008 4:43 pm

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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 12.04, Apache OpenOffice 4.x
User avatar
Posts: 17255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 6 guests