findFirst empty cells in calc

find empty cell in calc

Crtl+F and then just press Enter?

Hello

You may find the limit of one range by pressing simultaneously Ctrl and an arrow.

If the selected cell is actually inside a data range, Ctrl+Down will select the last cell, in the same column, downwards.

If it is on the edge of the data range, the selected cell will then be the first cell of the next data range, downwards.

By the choice of the arrow, you may decide in which direction, you are looking for an empty cell.

Then, you may find easily the requested cell.

Kind regards, Michel

I need to add more detail!!

I am using python and libreoffice 6.3.4.2.

I wrote a function to search backwards in a column to look for the first blank cell in that column.
example:

oRange = document.CurrentSelection

odesc = oRange.createSearchDescriptor()

odesc.SearchString = “”

odesc.SearchBackwards = True

result = oRange.findFirst(odesc)

I could not match an empty cell with this . At first I thought this was due to some oddity with an empty cell contents. But, after spending a lot of time, I discovered what appears to be a bug. This function works find EXCEPT it fails if the blank cell is part of a column of contiguous blank cells beginning with row 0 (in the API. Row 1 as it appears on the spreadsheet). That is, this function works with this column:

A1 2

A2

A3 5

But, this function fails with this column:

A1

A2 <blank cell’>

A3 5

Note that indicates a blank cell with a string value of “”, not the literal .

So this looks a like a bug in calc.

Added: This error occurs with a blank at either end of the column and with both forwards and backwards searching.

This same bug is apparent if I search using the find and replace menu options in the spreadsheet. I used ^$ for the search string and used the regular expressions options. Where do you report bugs?

Please tag your question with macro to help the helpers quicker and better help you.

Note that the info you report here are no answer, just complements to the issue description. It would be better to edit your question description and relocate them in here… next time.

Just to add, this same bug seems to occur whether you search backwards or forwards.

Hello,

here my proposal:

=SCBFFNE(B2:B6)
=SCBFFNE(B:B)
  • argument: a cell range reference (only the first column of the range is searched)

Here the macro function code:

rem SearchBackwardInArray
function SBIA(data() as array)
  	for i = ubound(data,1) to lbound(data,1) step -1  
	  rem print data(i,1)
	  if (isEmpty(data(i,1)) or data(i,1) = "" ) then
	  	SBIA="First Empty Cell Found at " & i
	    exit function
	  endif
  next     
  SBIA="No Empty Cells Found"
end function

Note:
If you dont want the cell index, but the cell reference,
just ask in the comments and i can modify the macro.

Hope it helps.

Thank you very much for your reply.
I had tried a workaround similar to the one you suggested, but did not place the data into an array. I just did multiple calls to the API. Thus, the search was slow with 200,000 lines of data. But, looking at your example, I put the data into an array and then, using python, once the data is in an array things go very quickly and I can use a variety of expressions to search for an “empty” cell. Thanks so much!

Glad i could help out.
Have a good one.