VariableRange sizes (XL2K)

Topic

New Reply

I am using DLookup functions like
=DSUM(ImportData,$A$7,I10:J11)
where ImportData is a named range. This works fine but the data is from a WebQuery and the number of rows can change each time it is refreshed.
At the moment I use code to find the size of the range and reset the range for the range name “ImportData”.
Is there any way to replace the range name with a function that will automatically pick up the size of the data?

Many Thanks

Peter

Reply | Quote

Replies

There was related discussion in this thread which may be of use. In one instance I used:

[Edited as I didn’t originally copy the first line.]

Range(“RangeName”, Cells(Application.Rows.Count, _
Range(“RangeName”).Column).End(xlUp)).Name = “RangeName”

to reset the rangename to cover all cells including blanks.

Reply | Quote

TY, I will go read that thread

Peter

Reply | Quote

On coming back to this I see I may have suggested that you do what you are already doing. Apologies if so!

Reply | Quote

Lets say that ImportData starts in A10 (the cell of the first numeric entry, thus not the cell of the label), use:

=DSUM(OFFSET(A10,0,0,MATCH(9.99999999999999E+307,A:A),1),$A$7,I10:J11)

Reply | Quote

Thanks for the formula Aladin.
Took me a while to work out what it was doing
I have got it tweaked to fit my setup now, just need to replace a couple of hundred formulas now!

Many thanks

Peter

Reply | Quote