+ Reply to Thread
Results 1 to 7 of 7

Assign a range using offset

  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003/2007
    Posts
    31

    Assign a range using offset

    Hi,

    I've created a variable to store a range (called 'SR'). I want to dynamically size this range using offset and by counting the number of entries in row 1 and column A and then assign it to the SR variable...how can I do this?

    This was my attempt but I get a 1004 run time error.

    Please Login or Register  to view this content.
    Cheers
    Last edited by teylyn; 06-03-2010 at 12:24 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Assign a range using offset

    Neutralizer, please use code tags when posting code. I'll add them for you, but just this time.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Assign a range using offset

    Neutralizer, I'm not sure I understand exactly what you want to select.

    Say, Row 1 has data in A1:H1 and column A has data in A1:A8. Active cell is D4

    Do you want to extend the range from D4 to the last row of column A and the last column of row 1? If so, try

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-31-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003/2007
    Posts
    31

    Re: Assign a range using offset

    Hi,

    No the selection does not need to be from the active cell. It actually needs to select all cells in the range A1, down the last used cell.

    I did this as an experiement:

    Please Login or Register  to view this content.
    Now this works except for the 'Set SR' line which is still giving an application or object defined error (1004).

    I'm doing something wrong with the 'Range()' I think.

    I know I could use xldown and xlright to do it, just trying to learn another way.

    Thanks

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Assign a range using offset

    Neutralizer, your code runs fine in my XL 2010 environment.

    UNLESS

    If either row 1 is completely empty OR column A is completely empty. Then your code bombs, because the count is 0 and that does not compute with Cells().

    You have to make sure that there are values there to count.

    The end(XLToLeft) does have its advantages. This code works, even if all cells are empty:

    Please Login or Register  to view this content.
    Note that the variables are Long instead of Integer, because an integer does not cover the whole range of possible row numbers.

    cheers

  6. #6
    Registered User
    Join Date
    05-31-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003/2007
    Posts
    31

    Re: Assign a range using offset

    That's interesting, just doesn't seem to want to run in 2003.

    Still I like your code there, thats just a better way. Cheers.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Assign a range using offset

    Neutralizer, your code runs fine in my Excel 2003 environment, too, but only when row 1 and column A have a value SOMEWHERE.

    Step through it and put a watch on the variables.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1