Select the cell above based on another cell's location

[mhr320]

I recorded a macro to search for the value AABRIEF, this word appears near the top of my document always, but is not always located in $A$12.

rem Search for the AABRIEF cell
args1(0).Name = "SearchItem.SearchString"
args1(0).Value = "AABRIEF"

The next step is to select the cell above AABRIEF and do the following:

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1:$A$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteRows", "", 0, Array())

As mentioned before, rather than simply going to cell $A$12, because AABRIEF is located in $A$13 will not always work because it may not always be in $A$13.

In the ".uno:GoToCell" below is there a way to select the cell the AABRIEF is located -1 cell?

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1:$A$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

[elmau]

What do you really want to do with the cell? In most cases, it is not necessary to select it.

[mhr320]

Thank you for responding. I want to delete all of the rows above the cell with AABRIEF as a value. It will be on a different line each time I import a file into Calc, but it will always be near the top and will always require deleting all of the rows above it.

[elmau]

All rows above… up to where? up to row one or is there another condition?

[JohnSUN]

Welcome!

In most cases, recording macros does not solve the problem. Yes, you can use it for some elementary operations that do not require logic. For example, highlight the current line, change the background color, move down one line, clear the background color, and move down one line again. Now, if you assign the resulting macro to some key combination and hold it down for a few seconds, you will get a million striped lines… If the word “if” occurs at least once in the description of your task, then the macro recorder will not help you - you will have to write a macro manually.
And in your task there are at least two “ifs”: if there is a cell on the current sheet with the word “AABRIEF” and if this cell is not in the first row.
But there is good news - the macro code written by hand will be much shorter:

Sub delRowsAboveString
Const STRING_TO_SEARCH = "AABRIEF"
Dim oSheet As Variant, oSearchDescriptor As Variant
Dim oFound As Variant, nRow As Long
Rem get ActiveSheet
	oSheet = ThisComponent.getCurrentController().getActiveSheet()
Rem create SearchDescriptor
	oSearchDescriptor = oSheet.createSearchDescriptor()
Rem set SearchString as Const STRING_TO_SEARCH
	oSearchDescriptor.setSearchString(STRING_TO_SEARCH)
Rem Try to find
	oFound = oSheet.findFirst(oSearchDescriptor)
Rem Is result correct?
	If IsNull(oFound) Then 
		MsgBox("String '" & STRING_TO_SEARCH & "' not found!", 0, "Wrong sheet?")
	Else 
Rem In which row string was found?	
		nRow = oFound.getCellAddress().Row
Rem Is this not a first row of sheet?
		If nRow > 0 Then oSheet.getRows().removeByIndex(0, nRow)
  	EndIf 
End Sub

Don’t you think that such code is a little easier to read than the one that creates the macro recorder?

[mhr320]

After macro is executed, I want no cells or rows directly above the cell containing the value AABRIEF.

[JohnSUN]

Forgive me for my misunderstanding. I read your comment several times, but I still don’t understand. As far as I understand the code given in the text of the question, the macro should clear the cells in column A above the cell with the text “AABRIEF” and delete all rows above this cell. (By the way, why clear them if you have to delete them right after that?). This is exactly what the code I showed does: it tries to find a cell on the active sheet with the text “AABRIEF” (I say “tries” because there may not be such a cell on the active sheet, right?); if such a cell is found, then the macro looks in which row the found cell is located. If this is the very first row, then deleting something above this cell is unlikely to work, right? Otherwise, the macro calls the .removeByIndex(from the first row, the number of rows to remove) method. So what was your comment about?

[mhr320]

Ok, I am sorry for my poorly explained scenario.

1. AABRIEF will ALWAYS be present in the file.
2. All rows above must be DELETED above the row CONTAINING AABRIEF

So I only need to find the cell reference for the cell containing AABRIEF and DELETE the rows ABOVE this cell so that the FIRST ROW, FIRST CELL contains the word AABRIEF

[mhr320]

And, I agree, writing out the Macro is preferred over using the Macro Record. Finding the resources to learn this myself has been challenging, plus I am restricted on the server I am writing this. This step is the first of many, I was hoping by asking for help on this step, I could ascertain what I am going to need to for the rest of it. Probably not though. StarBasic seems like a mythological beast to me.

[JohnSUN]

Okay, I’ll try again.

Have you tried running the delRowsAboveString macro I provided in my answer?

[Villeroy]

So you import text files? Just skip the offending lines instead of removing them afterwards.
Use 3rd option “From row”: