Here its a link to a OpenOffice page where it’s written at the end;
Searching for the regular expression ‘^$’ will not find empty cells. This is intentional - the rationale being to avoid performance issues when selecting a huge number of cells. Note that empty cells will not be found even if you are only searching a selection.
Does anyone know a way to search through a selection and mark empty cells?
Quoting @Dabola: “Does anyone know a way to search through a selection and mark empty cells?”
-1- I just tested with LibO V6.0.3 and found the behaviour described in the question changed.
Using the RegEx ^$ on a selected range or a multiselection now basically works as expected, but produces errors now and then under unclear conditions doing so.
-2- If resorting to user code you can apply the oneRange.QueryEmptyCells method to get a SheetCellRanges object returned being what’s expectable given the name.
By the way: What do you want to achieve once having selected the empty cells?
Edit 1:
Regarding the additional information given by the OQ I would suggest not to use any kind of ‘Find & Replace’, but the valuable tool of Conditional Formatting for the purpose.
I made this rather comfortable example to demonstrate the principles and the power of the tool. There you can change some cell contents in column M, and you get shown an overview as you seem to want on the fly.
Thanks for the sample file! This * is exactly what I am looking for. I have to learn how to use this >Format >Conditional Formatting. If I select “M” by your selector in cell M5, then how can I temporarily move all the blue lines up to the top of the sheet? Row 8,11,19 and 26. Its useful to see all these respondents side by side - for a minute - and then back again. Yes I know its possible by marking all and >Sort by, but is is possible by Conditional Formatting directly?
I thought CF was the appropriate means concerning your request.
It pays to learn about CF if you repeatedly have tasks of the kind.
However, there are flaws in CF, too.
My advice: Iplement the basic functionality of your sheets to a rather final state, and implement the CF you want in a last step. It’s useful, of course, to have CF in mind during the development.
“Get through the curve when you reach it, but be prepared in advance.”
Thanks for your help, Lupp!
I am working with questionnaire. One row for each respondent. Columns; reply of questions. Somtimes the questions are not replied, other times I want to use code for disunderstandings, direct errors, etc. Finally with non of these codes the cell will be empty.
Sometimes I just want to mark the empty cells for further editing. So I am looking for an easy way to search through a column and select cells that are empty. Or best of all; Select/mark all the respondents (lines) that has not answered question number 8 (that means column ‘G’). I want to mark column G, and by a very simple command search for all the rows with no answers/codes (empty cell). Finally; not only mark these cells in column G - but best of all; Mark the complete row for all the respontents with empty cells in column G.
Then I can change the background color for these respondents to light grey…
(This is not an answer to your question. Its partly an explanation answering my “by the way” better placed as a comment on my answer, partly additional information to your question better given by editing the original question. Don’t worry, however. Just consider the hint when you post again.)
I am sorry for all the comments. But this is the only way I am able to go on with the dialog. I find two buttons; >Add comment and >Edit your previous answer. No >Reply button. Do you have any tutorial on how to use this forum?
At the same time: The text asking the questions of the Robot Quest. have been translated to Vietnamese and Chinese. I dont understand the meaning, but when I see roadsigns I understand the meaning… All together; its not an easy forum to use for a newbie
Sequences of comments are the only way to organise a discussion in this site.
As long as you not already posted an answer (only 1 per user allowed - may be edited later as often as needed) you are shown an area for entering one.
There are lots of attempts by experienced users to get the powers behind this site to improve it. We only can try to get some attention for this concern by asking ‘meta’ questions. No success so far yet.
If you only want to see, which rows contain empty cells in column G, then you can use a Standard Filter from menu Data >More Filters. Select the range, then call the filter, choose the column G and set criterion to = empty. You can then e.g. give all the selected rows a background color. It will not effect the hidden rows, but only the visible ones. Thne mark all and show all rows.
Thanks Regina!
I am able to select all empty cells, change background color. Then I had some problem to switch off the filter. By undo I have two steps. I couldn’t do just the 2nd. Until I read exactly what you wrote; Mark all and show all rows. Then the alternative >Switch off the filter was no longer grayed out (by selecting all). If I add a intermediate value, ‘aa’ before I Switch off the filter, then I can select all and sort the data by column G, and give the whole line grey color.
You can first highlight the rows and check if it works for you. The guide is available here.
If that works, then you can use Data - > Auto filter and select “Empty” from filter dropdown.
This is to confirm that Lupp’s answer work with LibreOffice 7.0 or more recent
For those not familiar with using the Calc RegEx ^$ to select empty cells, below are the three easy and quick steps with screenshots. After the initial learning curve. It is just 4 clicks or so to search for empty cells. And optionally replace them with any value to your liking.
Three Steps:
Optionally, select a range of cells or a multiselection of cells. The number 1 in this screenshot show this.
This screenshot above is on a French device. But the buttons and their location is the same for any languages to your liking.
I suggest using this small batches of cells at a time. Not large number of cells. Because depending on your device, searching a large number of cells might use too many resources. In turn, this risk to freeze your Calc.
Using Calc Search and Replace:
Using the Search field, enter those two RegEx symbols ^$
Optionally, using the Replace field, enter anything to your liking. This value will replace all empty cell(s). In this example, I will replace all empty cells with a dot .
Using the Other options group, check the RegEx / Expressions régulières box.
Click on the Replace All / Tout remplacer button
This screenshot shows the end result. Where the cells A3 et A5 were automatically replaced with . Joy 
