Ask Your Question
0

How to search/select empty cells?

asked 2018-04-28 14:32:02 +0200

Dabola gravatar image

updated 2018-04-28 14:35:41 +0200

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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2018-04-28 14:53:54 +0200

Lupp gravatar image

updated 2018-04-28 17:14:25 +0200

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.

edit flag offensive delete link more

Comments

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?

Dabola gravatar imageDabola ( 2018-04-29 08:50:38 +0200 )edit

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."

Lupp gravatar imageLupp ( 2018-04-29 13:13:13 +0200 )edit
0

answered 2018-04-28 19:15:36 +0200

Regina gravatar image

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.

edit flag offensive delete link more

Comments

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.

Dabola gravatar imageDabola ( 2018-04-28 23:22:29 +0200 )edit
0

answered 2018-04-28 16:10:57 +0200

Dabola gravatar image

updated 2018-04-28 16:12:44 +0200

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.....

edit flag offensive delete link more

Comments

(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.)

Lupp gravatar imageLupp ( 2018-04-28 17:07:38 +0200 )edit

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

Dabola gravatar imageDabola ( 2018-04-29 08:32:08 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-04-29 13:00:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-04-28 14:32:02 +0200

Seen: 2,826 times

Last updated: Apr 28 '18