Hi.

I have an excellent piece of coding that I found in this group that inserts
rows. It does a great job of copying the formatting of the other rows except
the cell in each row that has a data validation code so the "record number"
it represents doesn't get repeated. I must manually fix that.

The "insert rows" code is this below, between the asterisks
*********************
Sub InsertROWS()

Dim i As Long
Dim j As Long
Dim k As Long

On Error GoTo dontdothat
Do
i = InputBox("How many rows do you want to insert?", "Insert Rows ",
1)
Loop Until i <> 0
Do
j = InputBox("At what Excel row number do you want to start the
insertion?", "Insert Rows", 10)
Loop Until j <> 0

k = j + i - 1
Range("" & j & ":" & k & "").Insert shift:=xlDown
j = j - 1
Range("A" & j & "").Select
Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""),
Type:=xlFillDefault
Exit Sub

dontdothat:

End Sub
*********************
The above works perfectly.

The data validation code checks for duplicates in the "record number" field
and it looks like this:

=COUNTIF($B$2:$B$145,B2)=1

Currently there are 145 cells in that column so the above is correct. But
if I were to add rows, that number would stay the same so I'd have to select
the column less the header cell, and manually adjust to however many rows are
being dealt with.

Is there any way to have the above "insert rows" macro to do this
automatically and correctly for us? Thks