Name a dynamic Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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
 
Back
Top