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
return
end if
end repeat
end tell
end tell
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:
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.
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.
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
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.
@gglick
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.
@gglick
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.
@Sleepy
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...
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.
@Sleepy
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
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.
