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

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

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