I have a perfectly functioning script that does everything that I need perfectly except for one thing. I have a column that typically (depending on current project) has sequential numbers ranging from 1 to (as many as needed, added as needed). The script I currently have clears these numbers as the final step. Is there a way to re-write this to not clear that range explicitly? I have been googling for two days on a fix for this to no avail.

Lets say I have 10 rows numbered 1-10. I need an 11th row, so I run script which adds row(s) depending on how many I type into inputbox. This finds the last row (10) with data in column B, then inserts below this row, then copies down entire 10th row, then clears entire new row (11) except formatting and formulas.

I cannot change sequential number to formulas (=b10+1) as this will break if the worksheet gets sorted by another column which has happened.

I could provide my workbook but there are numerous sheets and scripts which may confuse the matter.

I am also open to suggestions to discover last row as I know what I have isn't the cleanest way to do this. This worksheet starts with several hundred rows typically, and can grow to as much as 20,000 but should never get to the B200000 I use to get clear of any data.

My main concern currently is retaining numerical values in column B which has named range of "Weld_No" while inserting rows and preserving formatting and formulas. The reason for inserting rather than just copying down is I have named ranges which I need to stay inside of. I don't use the entire column as a named range because I have formulas which count blanks and it the entire column is named range it would throw this count off. I am also currently looking into converting my name ranges to OFFSET formula so they would grow with the data but that's off topic too.

I tried to address any questions or suggestions that I figured would come up.

Here is the current code I need to tweak.

Sub InsertRowsAndFillFormulas_caller()
     '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
    Call InsertRowsAndFillFormulas
End Sub
 
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
    Dim x As Long
    
    'This turns off screen updating to "hide" what he macro is doing
    'Application.ScreenUpdating = False
    
    'Use this to specify start point.
    Range("B200000").Select 'Just to get clear of any potential data
    Selection.End(xlUp).Select 'Goes up to the last weld number entered into column
    ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
    If vRows = 0 Then
        vRows = Application.InputBox(prompt:= _
        "How many rows do you want to add?", Title:="Add Rows", _
        Default:=1, Type:=1) 'Default for 1 row, type 1 is number
        If vRows = False Then Exit Sub
    End If
     
     'if you just want to add cells and not entire rows
     'then delete ".EntireRow" in the following line
     
     'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
    Dim sht As Worksheet, shts() As String, i As Long
    ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
    Windows(1).SelectedSheets.Count)
    i = 0
    For Each sht In _
        Application.ActiveWorkbook.Windows(1).SelectedSheets
        Sheets(sht.Name).Select
        i = i + 1
        shts(i) = sht.Name
         
        x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup
         
        Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
        Resize(rowsize:=vRows).Insert Shift:=xlDown
         
        Selection.AutoFill Selection.Resize( _
        rowsize:=vRows + 1), xlFillDefault
         
        On Error Resume Next
         
        Selection.Offset(1).Resize(vRows).EntireRow. _
        SpecialCells(xlConstants).ClearContents
    Next sht
    'This turns on screen updating which was turned off previously
    Application.ScreenUpdating = True
    
    Worksheets(shts).Select
    'ActiveSheet.Protect Password:="password", AllowDeletingRows:=True, DrawingObjects:=True, _
    Contents:=True, Scenarios:=True
End Sub
Thanks in advance.