VBA Insert row, then clear contents except for data in column B.

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.

Please Login or Register  to view this content.

Thanks in advance.

I have looked at a tables before, however they affected something negatively which I cant recall currently. Maybe it was column headers, conditional formatting, something that was a deal breaker. Thanks for the suggestion.

The scope needs to be limited to VBA solution. Modifying the sheet is not viable solution as this is corporate workbook which is distributed and used at multiple locations and for continuity and familiarity what the user sees should not be modified.

Thanks again.