Insert a block of sequential numbers into table

[sspatriots]

I'm trying to figure out how to insert a block of sequential numbers starting in the first empty cell (Lastrow + 1) of the 2nd column and going down until the last number is entered. This is in a table called "Table1" right now. I have no clue where to even start with this one. Any advice or a link to something similar that I could tinker with would be greatly appreciated. In a nutshell, my department is allocated a block of new PO numbers at a time, like "500" to "575". When they are used up, another allocation of numbers will be assigned, like "970" to "1025". I was thinking a macro with an inputbox to enter the first number in the range and then a second inputbox to enter the last number in the range and then all the new rows are added with these numbers populating downward in the 2nd column. Thanks, SS

 

[sspatriots]

I've been hacking at this all afternoon. Below is what I have so far. I get a "Run-time '1004': Method 'Range' of object '_Global' failed" error at the line directly below:

Selection.AutoFill Destination:=Range(D & Rows.Count).End(xlUp), Type:=xlFillSeries

I'm hoping someone can look at this one line and give me an idea how to make it work so that it fills in that range of numbers to the last row that is added. Thanks, SS

Sub InsertRows2()

Dim wb As Workbook

Dim ws As Worksheet ': Set ws = ThisWorkbook.ActiveSheet
Dim i As Long, x As Long
Dim tb As ListObject
Dim NewRow As ListRow
Dim C As String
Dim Start_No As String
Dim End_No As String
Dim TotalNewRows As String
Dim Lastrow As Long
Dim lrow As Long
Dim D As Range

   
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1") 'Added SPS,06/16/22, worksheet the table is on
   

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Assign a variable to hold our table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set tb = ws.ListObjects("Table1")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Find last row, if table header is in row 1 of worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
lrow = tb.Range.Rows.Count
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Find last row, if table header is not in row 1 of worksheet             LAST ROW (lrow) = 14
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
lrow = tb.Range.Rows(tb.Range.Rows.Count).Row
   
Start_No = InputBox("Enter Starting PO Number in Block")             'START NUMBER (Start_No) = 60
End_No = InputBox("Enter Ending PO Number in Block")                 'END NUMBER (End_No) = 67

TotalNewRows = End_No - Start_No                                     'TOTAL NUMBER OF NEW ROWS NEEDED FOR NEW BLOCK OF PO NUMBERS (TotalNewRows) = 7

Cells(lrow + 1, 2) = Start_No
C = Cells(lrow + 1, 2)

'i = Application.InputBox("How many rows would you like to add?", "Insert Rows", 1, Type:=1)
i = TotalNewRows
Set tb = ws.ListObjects(1)

For x = 1 To i
    Set NewRow = tb.ListRows.Add(AlwaysInsert:=True)
Next x

    With tb.Range.Columns(2) 'column_to_check is relative to the tb.Range
        Set D = .Find(what:=Start_No, after:=.Cells(1), LookIn:=xlValues, _
            searchorder:=xlByRows, searchdirection:=xlPrevious)
            
        If Not D Is Nothing Then
        
            Debug.Print D.Row + 1 'last empty row
                 
            D.Select
                 
            Selection.AutoFill Destination:=Range(D & Rows.Count).End(xlUp), Type:=xlFillSeries
                 
        End If
        
    End With

End Sub

 

[NoSparks]

You don't need to be concerned with any of the sheet cells, rows or columns.
Once you've got the start and end numbers this is all you need

With tb
    For x = Start_No To End_No
        Set NewRow = .ListRows.Add
        NewRow.Range.Cells(2) = x
    Next x
    Set NewRow = Nothing
End With

Heres a couple of links to pages dealing with tables

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

jkp-ads.com

 

[sspatriots]

That worked great. Thank you again for looking at this. I'm amazed that I even got as far as I did with it yesterday. Do you mind explaining what the "(2)" in the code means? I'm going to study this to make sure I can use it again down the road.

NewRow.Range.Cells(2) = x

 

[NoSparks]

Do you mind explaining what the "(2)" in the code means?

In words, explaining that line a little bit backwards, it's
the second cell (2), in the range that NewRow is set to (which is a single row)
if the range was more than one row it would need to include which row of the range it refers to.
I guess best practice would be for that line to have been written

        NewRow.Range.Cells(1, 2).Value = x

Hope that helps