Dynamic Named Range with Moving Data Below

Question

I am trying to create a dynamic named range that I can use for a data validation list. I use these all time but in this case I have information that is housed below the range that cannot be counted in the range. Also, I have a macro that insert rows within this range that do need to be counted.

I normally would use something like this if nothing else was in the column: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

I need to start this one down the page a little ways so I used: =OFFSET($A$24,0,0,COUNTA($A$24:$A24),1)

Notice I have removed the "$" before the last "24" in the formula hoping it would expand accordingly, but that does not seem to be consistent.

Basically, I need the COUNTA range to only include a range of cells that will always be growing and shrinking.

I'm not bad in VBA and am open to a solution that might include looping through a range of cells and stopping once it reaches a cell that's value equals a certain text string (in the case in would be .Value = "Request 1"). But I am a little apprehensive about feeding a form or ActiveX Control, as this has caused me issues in the past with viewing and printing functionality.

Devin Roberts Devin Roberts · Accepted Answer · 2017-10-03T20:34:18

I used a the following code to create a range elsewhere in the workbook that I could then easily use to create a dynamic named range:

Sub UpdateEntities()

Dim i As Long, x As Long

i = 24
x = 1

Sheets("Values").Range("AH:AH").ClearContents

Do While Cells(i, 1).Value <> "REQUEST 1"

Cells(i, 1).Select

If ActiveCell.Value <> "" Then
Sheets("Values").Cells(x, 34).Value = ActiveCell.Value
i = i + 1
x = x + 1
Else
i = i + 1
End If

Loop

End Sub

Dynamic Named Range with Moving Data Below

1 Answers