Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find max number in a table and add 1


I have a table with numbers used as a unique identifier.  I need to find the max number in that column and add 1 to it. I need it to not change when I filter the column. Is there a formula I can use?   If not what would a macro look like?   Thank you!



A VBA macro isnt needed here- did my Answer below solve your problem ? 
John_Ru (rep: 3222) May 4, '22 at 5:57 am
Did you see my Answer below? Seems that the email alerts stopped working (following a site update) so you may have missed it.
John_Ru (rep: 3222) May 6, '22 at 12:32 pm
Add to Discussion



Hi Josh(?) and welcome to the Forum.

The Excel worksheet function MAX isn't affected by filtering so you can use that.

If your identifiers are in column B say (and nothing above or below your table), this formula will work:


where the B:B bit in bold is the range for which the largest number is returned by MAX (and 1 added to it to give your "Next available identifier").

It's better to point to the column of the table containing your identifiers however. If you've put a formula like the above in a cell, you can edit the B:B bit  and select the part of the table containing the identifers- click in the column header and drag the cursor to the bottom then Enter. You'll then get a formula like this: 


where my second column in the table holds those numbers.

Hope this makers sense and solves your problem.


Answer the Question

You must create an account to use the forum. Create an Account or Login