Index each row in named table

John Doe 1 Reputation point

I created a table in Excel, named "Table1" lets say. I have a column "ID". I need a VBA script to apply an incremental number for each row (i.e 1, 2, 3, 4, etc.) automatically. I am trying to create a unique identifier for each row for this table so it can be the primary key in Power BI. A form will add a row to this table, so I am hoping this script will generate the next ID number right away also.


Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,461 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Oskar Shon 866 Reputation points MVP

    You should use loop with incremental variable like:

    dim i&  
    dim last_row&: last_row = cells(rows.count, "B").end(xlup).row 'test in B column'  
    for x = 1 to last_row  
    i = i +1  
    cells(x,1) = i 'tape index in A column'  


    0 comments No comments