Generate Auto Number Using VBA

Hello All

I have in my sheet the below formula:
=IF(ISBLANK(H12),"",A11+1)
To generate an automatic sequence number, depend on the cell in H column if it is not blank
I have a problem in above formula; when delete one row the sequence number following that deleted row gives errors

Whatever

I think to change it to VBA
May I have vba code doing the same job? But without any problem when deleting any row and keep the others row numbers the same without change.
I hope it is clear

see attached

Hi there,

A defined name formula (native Excel, not VBA) might be the way to achieve this. In which cell are you inserting the formula:

Please Login or Register  to view this content.

Even better, can you post a sample of your worksheet here so we can see where the various values are coming from?


Regards,

Greg M

Originally Posted by Greg M

Hi there,

A defined name formula (native Excel, not VBA) might be the way to achieve this. In which cell are you inserting the formula:

Please Login or Register  to view this content.

Even better, can you post a sample of your worksheet here so we can see where the various values are coming from?


Regards,

Greg M

same problem

if possible to be by VBA

Thanks

Hi again,

Thanks for posting your workbook - I hadn't seen your reply when I sent my message

Take a look at the attached workbook and see if it does what you need. It contains a defined name formula "forAutoSum" which is used to autonumber the cells in Column A.

The defined name function will continue to operate correctly even when rows are deleted. (Mind you, deleting a row seems to generate error conditions within your existing VBA code!)


Anyway, hope this helps - please let me know how you get on.

Regards,

Greg M

Thank you Greg M for your reply, you are so polite

Auto number is working but when deleting any row it gives the same number to the next row


I want every row to keep its number, I mean when any row get its generated number; it must be kept the same.


I prefer to have vba because there are many users for this sheet and it may somebody delete the formula so I prefer to be by vba

Please Login or Register  to view this content.

it will be great if anybody help me modify the above code to be used in my sheet

thank you

Hi again,

Many thanks for your very prompt feedback.

Sorry, I didn't realise that an autonumber should remain constant once it has been assigned.

The attached version of your workbook uses a hidden helper Column A which allows rows to be deleted while maintaining the original values of the autonumbers.

By the way, I tweaked your VBA code so that deleting a row no longer causes it to crash.


Hope this helps - please let me know how you get on.

Regards,

Greg M

it is clever idea to generate auto number, but in my sheet adding column or hide one is affected other vba codes in my sheet and that is what happened with me.


I wish to have VBA code so it is not affect anything in my sheet or obliged me to use, add, other cells.


Thank you my friend

Good Morning All

Any expert here can find VBA code?

any body can help please

Hi again,

Clear all of the formulas in Column A and then modify the start of your code as per the highlighted rows:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg M

Are you magician

kidding

it is perfect, I put this question in many excel forum but no one give me what I need exactly

I really appreciate your help and support

you keep patient form the beginning even my question and language is not clear

thank you again and I wish you all the best

I am sorry I did not understand what is the cross-post mean

but now it is clear for me


sorry

Hi again,

Moderators: I hope I'm allowed to reply here now that the cross-posting issue has been cleared up.

Stoicy: Many thanks for your feedback and also for the reputation increase - much appreciated

You're very welcome - I'm glad I was (finally!) able to help.

Best regards,

Greg M