Custom Excel Function For Next Highest Prime Number

I’m still having way too much fun on Pastebin.com. Below is a formula I started while working out, but ended up getting a little more fancy after I tried to clean it up to run quicker. It calculates the next highest prime number after a given number (example: =NEXTPRIMENUMBER(100) = 101)

There are some regular Excel Array formulas that can be used to test true or false on integers being prime, but they are limited to testing values matching row counts, so no higher than 1.05 million (boring!). I did run into a weird VBA bug where I discovered the vba Function MOD ALWAYS converts its values to LONG parameters. I discussed this a little on Stack Overflow as this became an issue when trying to do bigger numbers.

Anyway, check this out if you’re bored or if you’re dying to know what the next highest prime number is after 10 trillion (it’s 10,000,000,000,037). It does this calculation in under 4 seconds on my laptop. Initially it was taking about 5 seconds to do a billion, but I cleaned up some redundancies, and it’s pretty good now. If anyone can enhance (using EXCLE vba), I’d be curious to see.

Best Method for LastRow/LastColumn In Excel

It’s been awhile since I’ve posted, but I’ve just started messing around with PasteBin and so I’ll post something I put there.

I think the most common beginner VBA question is “how do I find the last row of a sheet?” There are dozens of answers out there, but there are better ways to accomplish this. In my opinion, the best way is below using the below VBA script which I’ve turned into a formula you can paste into your file and get your answer. My post includes text stating why I believe it’s better. Other methods are vulnerable to uneven columns, usedrange being out of sync, or filtered rows. Using the FIND method avoids a lot of confusion.

This particular formula has some if statements so if a user selects an entire column or columns, it will return the lowest row in that range.

Excel should have this function!