Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VariableRange sizes (XL2K)

    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

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VariableRange sizes (XL2K)

    TY, I will go read that thread

    Peter

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VariableRange sizes (XL2K)

    There was related discussion in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=110212>this thread</A> 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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VariableRange sizes (XL2K)

    On coming back to this I see I may have suggested that you do what you are already doing. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Apologies if so!
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VariableRange sizes (XL2K)

    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)
    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VariableRange sizes (XL2K)

    Thanks for the formula Aladin.
    Took me a while to work out what it was doing <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I have got it tweaked to fit my setup now, just need to replace a couple of hundred formulas now!

    Many thanks

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •