How to add Auto Numbering in Excel

happy 1001
happy 1001 used Ask the Experts™
on
Hi Experts

I have an excel sheet in which there are 2 types of Rows. The green colored rows are the MAIN CATEGORY Rows and each of these has got further SUB CATEGORY Rows which have yellow colors.

Now I want to create this list in such a way that when I add any NEW ROW to this list then it AUTOMATICALLY gets the correct number in the Column B, without needing me to enter it manually. Please have a look at the attached snapshot to understand it. I have also attached the excel sheet.

Auto-Numbering.png
Please suggest some way to do this.


I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office Professional Plus 2016 x64
and Windows 7 x64

Thanks a lot for any help.

Regards
Excel---Auto-Numbering.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
try:

Private Const Color = 5287936
Private Const targetCol = 2

Sub putAutoNumber()
    Dim BigNo As Integer
    Dim SmallNo As Integer
    BigNo = 0
    SmallNo = 0
    lastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
    For i = 2 To lastRow
        If Cells(i, 1).DisplayFormat.Interior.Color = Color Then
            BigNo = BigNo + 1
            SmallNo = 0
            Cells(i, targetCol) = BigNo
        Else
            SmallNo = SmallNo + 1
            Cells(i, targetCol) = BigNo & "." & SmallNo
        End If
    Next
End Sub

Open in new window

Ryan ChongSoftware Team Lead

Commented:

Commented:
This might work for you too, depending on what you want done.
Place it in the VBA code window for the sheet where you want it to run (e.g. Sheet1 (Sheet1))

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim row As Integer
    If Target.Address = Target.EntireRow.Address Then
        row = Target.row
        If Cells(row, 2) = "" Then
            Cells(row, 2).Value = Cells(row - 1, 2).Value + 0.1
        End If
    End If
End Sub

Open in new window

This will not renumber existing entries, and may not be what you want when it gets up past .9

Note that you may need to re-save your workbook as macro-enabled xlsm.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016
Commented:
Hi,
if your criteria is the colour then
pls try

Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function
Function IsSameCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
    xcolor = criteria.Interior.ColorIndex
    If range_data.Interior.ColorIndex = xcolor Then
        IsSameCcolor = True
    Else
        IsSameCcolor = False
    End If
End Function
Function CountSubCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountSubCcolor = CountSubCcolor + 1
    Else
        CountSubCcolor = 0
    End If
Next datax
End Function

Open in new window

Regards
Excel---Auto-NumberingV1.xlsm
Michelle GilbankMarketzone Database Specialist

Commented:
happy,
I believe the simplest way would be to simply copy an existing row, either yellow or green.
This can be done without creating a macro or adding code.

To do this, type the following in cell B2:
=IFERROR(INT(B1+1),1)
...and type the following in cell B3:
=B2+0.1

you can now right-click on either row 2 or 3, and copy it, and then paste it to the bottom of your list.

if you then copy cell B2 and paste into all of your existing green rows' B column cell, you can copy any existing green row to create a new row without needing to use row 2

if you then copy cell B3 and paste into all of your existing yellow rows' B column cell, you can copy any existing yellow row to create a new row without needing to use row 3

:-)
teslasmom

Author

Commented:
Thank you experts. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial