Excel VBA Named Range for Form Keeps Changing

Question

I created a userform in VBA where you can add and delete records and I am using a named range to refresh the list box showing the data in the source. The issue I am running into is the named range keeps decrementing each time I delete a record. The main issue I am running into is if the record in the first row of data ($A$2) is deleted it changes the

What I want it to stay as so it does not error out when a record it deleted from A2: =OFFSET('Carrier List'!$A$2,0,0,COUNTA('Carrier List'!$A$1:$A$1000),11)

What it changes to when a record is deleted from A2: =OFFSET('Carrier List'!#REF!,0,0,COUNTA('Carrier List'!$A$1:$A$999),11)

I am calling this range in several functions, namely when the form is opened, when a record is added, when a record is edited, and when a record is deleted. It only causes issues when a record is deleted but it pretty much leaves the app dead in the water when that happenes.

VBA I am using

when form is opened:

Private Sub UserForm_Initialize()

Me.lstbxCurrCarrierLst.RowSource = "CarrierList"

End Sub

For the add,edit,delete functions:

Me.lstbxCurrCarrierLst.RowSource = "CarrierList"

So, is there a way I can lock the field range so it does not change OR is there another way I can set the RowSource so it stays constant as there are changes in the dataset?

Answer 1

Consider using Index() instead of Offset() in the range name definition. With Index() you can point to row 2 and you don't need to worry about deleting row 2.

The first Index addresses row 2 in column A, the second Index uses the row identified by the CountA function. The colon operator connects the two cells into a range.

=Index('Carrier List'!$A:$A,2):Index('Carrier List'!$A:$A,COUNTA('Carrier List'!$A$1:$A$1000))

Edit: I missed that the original named range is 11 columns wide, i.e. starts in column A and extends to column K. To do this in the named range formula, simply change the second Index() to work in column K, but keep the CountA for column A.

=Index('Carrier List'!$A:$A,2):Index('Carrier List'!$K:$K,COUNTA('Carrier List'!$A$1:$A$1000))