How to select next non-empty cells in a Numbers app?

Is there a way to select all next non-empty cells in numbers app?

For example, I'm now at A1 and A5 is empty. ▼

By what means I could select A1 to A4 in an instant?▼

After some search, I've found this method to jump to(but not selecting all the non-empty cells) the next non-empty cell by this AppleScript ▼

--finds next next blank (or non-blank) cell in column
--select starting cell, and run
property searchFor : missing value
tell application "Numbers"
  set t to front document's active sheet's first table whose selection range's class is range
  tell t's selection range
  set currentRow to its first cell's row's address
  set searchCol to its first cell's column's address
  end tell
  tell t's column searchCol
  repeat with i from currentRow + 1 to count cells
  if cell i's value is searchFor then --add not if want to find non-blank
  set t's selection range to cell i
  end if
  end repeat
  end tell
end tell

▲Source: How do I move/find the next non-empty cell - Apple Community_2019-09-25

Since I hardly ever know about AppleScript, it seemed quite hard for me to work this out even if after finishing some AppleScript tutorial. And it's kind of urgent,
so I was wondering could anyone help this out?

It seems like the key to solve this problem is this line of code▼

set t's selection range to cell i

maybe change to something like this?▼

set selection range to range???

You could try the AppleScript above by using the macro below:

Find next non-empty cell.kmmacros (4.9 KB)

Some related source if needed: AppleScript and Numbers: Defining and Selecting Ranges_2019-09-25

Are you talking about using KM to solve this? Or AppleScript? The answers are yes and I have no idea, respectively. I can help with KM but not with AppleScript.

The answer to any question that starts "Can KM" is yes. If a human can do it, then KM can do it. Period.

There's a third option and that is to use Numbers only. I presume you've investigated that. Is the answer no?

One question I have is what do you mean by "next". Did you mean "vertically consecutive"? Or did you mean something else?

May I presume that the currently selected Numbers cell is in the top location of the column you want to search? Or what other form of designating column do you wish to use?

Can you guarantee that there is at least one empty cell as you go down, or could you just hit the bottom of the spreadsheet because all cells in a column haven contents? It will be easier if you guarantee an empty cell.

Oh wait, you used the word "Excel" not "Numbers" in your question. In that case I believe there is a way to select only non-empty cells without resorting to KM or AppleScript.

Select the column that you want to work on then press FN+F5. Then click on Special. You will see a special menu for selecting blanks, and a variety of other weird choices. I can't tell if that includes non-blanks because I don't have Excel. You'll have to test that yourself.

▲The possibility seems quite small, according to what I've searched on the internet.

▲Any effective method is appreciated.

▲Yes in this example.

▲ Yes in this case.

▲Yes, I can assure that.

if my Excel tip doesn't work for you, you can probably use KM to create a loop to copy the current cell, check if it's empty, and move down using the Down Arrow key if the current cell is not empty. Then when you get to the first non-empty cell, move up one. Then we are at the bottom. To select everything upwards there might be a special key sequence in Excel, but if there isn't you can press SHIFT-UP as many times as you previously pressed down.

That's the approach I would use. Is that a good lead for you? I'm afraid I need to catch some sleep now.

Yeah, that must be working great. The only question here is when the cells is too much, it can be relatively slow.

Sorry if this sentence 'As in pressing Shift+Ctrl+Down Arrow Key in an Excel app.' confuses you. What I really mean is that, comparing to use 'Shift+Ctrl+Down Arrow Key' to get it done in an Excel app. Is there a way to do this in a numbers app. Probably because my English is not that good :stuck_out_tongue_closed_eyes:

I don't know of a way offhand to only select cells with values in Numbers the way you can in Excel, but let me ask you this: what is it you want to do with these cells once they've been selected? Depending on that, you may not need to worry about only selecting non-empty cells.

Imagine you have Column 1 configured as follows: five non-blank cells, five blank cells, five non-blank cells, five blank cells, five non-blank, and so on.

Now, if the currently selected cell is A1 and you press Command-Right Arrow once. You'll now be selecting A1 to A5. This is really useful if you have thousands of rows/columns and you're trying to quickly find the blank ones and do something about it.

I used to use Shift+Ctrl+Arrow Key in Excel to have this done for many times and it's quite convenient. But it seems impossible here in a numbers app. and you know, The experience of Excel on a Mac is terrible. So I'd like to find a way to sort this out.

Well, if you're just trying to quickly find blank cells in a given column, what about using the Filter function, under Organize in the inspector?


But usually I would want to cut the selected cells and repaste them to somewhere else. It wouldn't be very efficient using filter I presume.

In addition, the AppleScript above seems pretty close to the answer since it can let you jump to the next empty cell without waiting for a long time or doing some extra clicking or something like that.

Since you haven't told us how many cells the spreadsheet may contain, or how fast you need the macro to run, we can't worry about speed at this point in time.

Somewhere else? Where is that? If you explain the entire problem then maybe we can find the best solution for you.

I have more than one trick up my sleeve. I think I can write a KM macro that would be very fast that can solve this. But it would help if you explain the entire problem.

I wrote a macro that seems to do what I think you say you want to do.

But keep in mind that I don't understand the whole problem. So this may not be the best approach to solve your problem, even if it works.

Select all Non-Blank cells under current cell.kmmacros (7.9 KB)

If you are actually using Excel, then you could use this KM Action with that keystroke:


Is there some reason this simple solution will not work for you?

All right. Take this task I've been working on for example.
There is a table of around 700 lines, and what I need to do is constantly adding some new lines into the middle of it. Since I'd always like all of the lines have their own numbers assigned, so once I got a new line added, such as

Then in order to make the number right again, I have to select from E200 all the way down to the bottom (approximately 400 lines), then paste them from E199 like this

Your macro above is working great on some short amount of lines, but since every copy needs time, it does cost you much time to wait when it comes to lots of data like in my case. So I was wondering if AppleScript or any other approach will process faster...:stuck_out_tongue_winking_eye:

Yeah, It's working great on an excel, so I was just felt disappointed at numbers app without having such a handy shortcut.

Thank you for telling me the big picture. Now I can see the whole problem so I can direct you to an even better solution.

To solve this problem you don't need to use KM at all! All you need to do is learn how to use a certain Numbers function. That function is called "=ROW()". If you put that function into the entire column, then the entire column will always be correctly numbered exactly as you want.

If you do this your problem is 100% solved. I just tried it out and it's fabulous. To be honest, I didn't know about the ROW() function in Numbers but I've used some formulas and I figured Numbers would have this, and it does.

There you go. The perfect simple solution.

Great!That's indeed the best way to this specific question I have to say.
But for many other non-sequential number situation... Believe me I've got lots of them :joy:

You are welcome. Glad I could help. This is why everyone needs to explain the whole problem, because half the time they are looking for a non-optimal solution. And while I'm not a wizard at Numbers Formulas, I could probably help you with similar problems, except that this isn't a forum for Numbers support.