Help - Create a range that will update automatically to be used in formula

[Ready]

Hi
I use a button on my toolbar to run the macro below which updates an
existing range when new data is added:
The range Arg300227data is in a sheet named 'Data Entry' currently
goes from cell A1 to M4500

Sub SetArg300227DataRanges()

Application.Goto Reference:="Start"
ActiveWorkbook.Names("Arg300227Data").Delete
Selection.CurrentRegion.Select

ActiveWorkbook.Names.Add Name:="Arg300227Data",
RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion

End Sub

The person who created the sheet has since left the company but in
another sheet 'Cost Code Summary' used the formula below:

=IF(SUMIF('Data Entry'!$D$1:$D$4500,'Cost Code Summary'!A6,'Data
Entry'!$O$1:$O$4500)=0,"",SUMIF('Data Entry'!$D$1:$D$4500,'Cost Code
Summary'!A6,'Data Entry'!$O$1:$O$4500))

The problem now is the data that has gone past row 4500 is being
ignored. I know I could just keep replacing the 4500 with a higher
number but would like to automate this by clicking my macro button.

I would like to know how to modify the formula above so that I can get
rid of the hard coded cell references and replace them with my range
name "Arg300227Data" but only reference column D and O. The idea being
that when some new data is added the data entry person can just click
the macro button not have to worry about updating formulas.

Not every entry in columns D & O have data in them, otherwise I could
probably solve this with my basic VBA skills

Hope it is something simple and many thanks if someone can help

Ready

 

[Bob Phillips]

Add more names

Sub SetArg300227DataRanges()

Application.Goto Reference:="Start"
ActiveWorkbook.Names("Arg300227Data").Delete
Selection.CurrentRegion.Select

ActiveWorkbook.Names.Add Name:="Arg300227Data", _
RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion
ActiveWorkbook.Names.Add Name:="Arg300227DataD", _
RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion.Columns(4)
ActiveWorkbook.Names.Add Name:="Arg300227DataO", _
RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion.Columns(15)

End Sub

and then use

=IF(SUMIF(Arg300227DataD,'Cost Code Summary'!A6,Arg300227DataO)=0,"",
SUMIF(Arg300227DataD,'Cost Code Summary'!A6,Arg300227DataO)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)