Name a dynamic Range
 

I am currently using the following formula to name a range of data in a list
whic is regularly updated.

=Offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),35)

I wish to create another range of data from the same sheet which contains 35
columns - I want the range to consist only of the rows in which column "U"
is blank.
I would also like the range to be dynamic so it updates as new rows are added.

thanks for your help.

Regards

Mark

Name a dynamic Range
 

Mark,

You would need to use the worksheet's change event: copy the code below, right-click the sheet tab,
select "View Code" and paste the code into the window that appears. Of course, modify the name and
the range to suit your requirements - "35 columns" isn't very specific, but I started with column
A....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
ThisWorkbook.Names("myName").Delete
ThisWorkbook.Names.Add "myName", RefersTo:="=" & Intersect(Sheet1.UsedRange, _
Sheet1.Range("U:U")).SpecialCells(xlCellTypeBlanks ).Offset(0, -20).Resize(, 35).Address

Application.EnableEvents = True
End Sub



"Mark Campbell" wrote in message
...
I am currently using the following formula to name a range of data in a list
whic is regularly updated.

=Offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),35)

I wish to create another range of data from the same sheet which contains 35
columns - I want the range to consist only of the rows in which column "U"
is blank.
I would also like the range to be dynamic so it updates as new rows are added.

thanks for your help.

Regards

Mark

Name a dynamic Range
 

Mark,

Well, what you describe is a little different. You can use a formula to create a named range with
blank cells, _BUT_ only if you have a consistent layout, and the blank cells are all in one block,
and column T is filled whenever column U is filled. This example defined name formula is designed
for a list that starts in row 1 - you would need to account for blank rows above your data set....

=OFFSET(Sheet1!$T$1,COUNTA(Sheet1!$U:$U),0,COUNTA( Sheet1!$T:$T)-COUNTA(Sheet1!$U:$U),1)

HTH,
Bernie
MS Excel MVP


"Mark Campbell" wrote in message
...
Thanks Bernie,

I am not actually looking to delete or remove the existing range.
Really what i am trying to do is create new a range from a list of data on a
work sheet - with only the rows in which column U is blank.

I use this range in a combobox on a userform making selections to populate
text boxes. By creating new ranges I am trying to reduce the number of rows
in the combobox the user must croll through.

Is that what your code will do?
If I wanted to create another range from the same data list -
with only the rows in which column U are blank but column T must not be
blank -
can the code be amended to do this.

I had thought I would have been able to enter a fromula in the name ranges
window to do this.

"Bernie Deitrick" wrote:

Mark,

You would need to use the worksheet's change event: copy the code below, right-click the sheet
tab,
select "View Code" and paste the code into the window that appears. Of course, modify the name
and
the range to suit your requirements - "35 columns" isn't very specific, but I started with column
A....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
ThisWorkbook.Names("myName").Delete
ThisWorkbook.Names.Add "myName", RefersTo:="=" & Intersect(Sheet1.UsedRange, _
Sheet1.Range("U:U")).SpecialCells(xlCellTypeBlanks ).Offset(0, -20).Resize(, 35).Address

Application.EnableEvents = True
End Sub



"Mark Campbell" wrote in message
...
I am currently using the following formula to name a range of data in a list
whic is regularly updated.

=Offset(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),35)

I wish to create another range of data from the same sheet which contains 35
columns - I want the range to consist only of the rows in which column "U"
is blank.
I would also like the range to be dynamic so it updates as new rows are added.

thanks for your help.

Regards

Mark