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

Sequential numbering



I have 5 columns with 35 rows. I use this  to book in parcels I receive from residents.. When a row is filled in I also send this data to another page. When the parcel is collected, the details on the "next page" are saved to file as proof of collection. I have coded that when the parcel is collected, the row with the data has its contents cleared, so that the row can be used again.

Currently I manually add a number to the parcel along with apt number and name. I would like to add a column with heading of number and the rows to follow sequentially each time a row becomes blank.

            So if row 1 has the number 1

                    row  2 has the number 2

                    row 3 has the number 3

Then if row 1 contents are cleared  then the next number for this row would become 4

I would like to do this up to 999 and restart the count .

Is it possible to do this?



I'll answer your question now but it would have been better if integrated with your existing code. Next time, please remember to attach a sample .xlsm file to your question please. No personal data though.
John_Ru (rep: 5572) Jul 25, '22 at 8:13 am
Add to Discussion


Selected Answer


In the attached file, I've used this code to do what you describe.

If you pick a single cell in A2:A36 (of Sheet1) and delete the contents, it will add the next number, clear the four columns to the right and (via conditional formatting) highlight that row for you (so you can see more easily where to add your next shipment details):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SeqNos As Range

Set SeqNos = Range("A2:A36")

If Target.CountLarge > 1 And Intersect(Target, SeqNos) Is Nothing Then Exit Sub

If Target.Value = "" Then
    Application.EnableEvents = False
    With Target
        .Value = Range("H1").Value + 1
        'If >999 then reset
        If .Value >= 1000 Then .Value = 1
        'store new value
        Range("H1").Value = .Value
        'clear other columns
        .Offset(0, 1).Resize(1, 4).Value = ""
    End With
    Application.EnableEvents = True
End If

End Sub

You'll see that the "Last added." number is stored in H1 (and columns G:H are hidden, H1 being "35" as the file leaves me). You can set your current manual last number there (or set it to 999 to check that the numbers re-start at 1 when you exceed 999). The Target.CountLarge test means you should be able to paste (values) of your existing data into A2:E36.

Note that the Conditional Formatting rule is set for cell A2 as:


then the rule edited to apply to A2:E36. Once you type in column B, the row will go clear again. 

Hope this fixes things for you.



Tony- did you see that Answer (and did it solve your problem) ? 
John_Ru (rep: 5572) Jul 27, '22 at 3:42 am
Hi John
apologies with late reply. Have been very busy with work. What you have provided is exactly what I was hoping for. Thank you very much
mycobblermends (rep: 6) Jul 28, '22 at 6:44 am
Great. Thanks for selecting my answer, Tony. 
John_Ru (rep: 5572) Jul 28, '22 at 8:45 am
Add to Discussion

Answer the Question

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