+ Reply to Thread
Results 1 to 8 of 8

Contiguous Range Reference

  1. #1
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Contiguous Range Reference

    This has to be a simple issue but I am blind to my own ignorance. I want to select a contiguous range of cells, some are empty some contain data. This is the simple one line of code that is throwing the 1004 error. Sheet 3 exists and only one workbook is open. What am I missing?

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 01-23-2020 at 11:52 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Contiguous Range Reference

    Hello gototcm,

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Contiguous Range Reference

    Quote Originally Posted by gototcm View Post
    . . . This is the simple one line of code that is throwing the 1004 error. . . .
    Sheets("sheet3").Range(Range("A1"), Range("D10")).Select
    The best approach for this has already been given. I'll address what didn't work with this.

    Unqualified Range(...) calls implicitly refer to ActiveSheet, so the statement above is effectively the same as

    Sheets("sheet3").Range(ActiveSheet.Range("A1"), ActiveSheet.Range("D10")).Select

    If Sheets("sheet3") isn't ActiveSheet, then its .Range property won't accept arguments referring to ranges in other worksheets. Probably best to get used to

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    10-05-2019
    Location
    Work: East Coast; Home: Texas, USA
    MS-Off Ver
    Mostly 2010 ( Windows 10 & 365 Insider Super Fast )
    Posts
    32

    Re: Contiguous Range Reference

    Along the lines of what hrlngrv said, generally, unqualified Range(...) refers to the Application Range, ( https://docs.microsoft.com/en-us/office/vba/api/excel.application.range ) Property , and if not applied to an object, it will default to the Active sheet. This will be the case, for example when using the coding in a “normal” code module, as is more often the case..
    If applied to an object, the property is relative to the object.
    If you were to use unqualified Range(...) within a worksheet code module, you are effectively using the Range property of that worksheet object, and , in that case , you are not defaulting to the active worksheet, but to that worksheet
    Molly


    Edit: https://docs.microsoft.com/en-us/off...lication.range
    Last edited by MollyBrennholz; 02-06-2020 at 05:46 AM. Reason: At 10 posts count i can add the URL asd a URL

  5. #5
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Contiguous Range Reference

    Great, thanks for answering.

  6. #6
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Contiguous Range Reference

    I oversimplified my question that the essence of the problem was missed - but you caught it and thanks for reading between the lines. I think I understood your explanation but will have to do a bit more reading. Thanks for answering,

  7. #7
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Contiguous Range Reference

    As I replied to others, I made this question too simple and I missed the slight complexity. What I meant to write is

    /
    sheets("Sheet3").Range (range("named cell"),range("D" & x)) where x is a row number of a dynamic range.
    /

    But thanks for responding.

  8. #8
    Registered User
    Join Date
    10-05-2019
    Location
    Work: East Coast; Home: Texas, USA
    MS-Off Ver
    Mostly 2010 ( Windows 10 & 365 Insider Super Fast )
    Posts
    32

    Re: Contiguous Range Reference

    I think named ranges can’t have spaces, so you would have like namedcell , not named cell

    It would be best to be on the safe side and use this
    Worksheets("Sheet3").Range(Worksheets("Sheet3").Range("namedcell"), Worksheets("Sheet3").Range("D" & x))

    Some people do this

    With Worksheets("Sheet3")
    .Range(.Range("namedcell"), .Range("D" & x))
    End With

    That is what hrlngrv was explaining

    I like to do this
    Application.Range("=Sheet3!namedcell:D" & x & "")
    But few people do that. I have unusual preferences

    Molly

    ( Edit : just for others info .. I think this thread is related to this 'un https://www.excelforum.com/excel-programming-vba-macros/1303726-range-reference-formula.html
    https://www.excelforum.com/excel-pro...e-formula.html
    )
    Last edited by MollyBrennholz; 02-06-2020 at 05:48 AM. Reason: At 10 counted posts i can add the URL as URL

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. non-contiguous named range - how to use in formulas, or how to convert to contiguous list
    By david killoran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-23-2019, 03:33 AM
  2. [SOLVED] Copy range and paste into non contiguous range
    By amros in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-02-2017, 05:42 AM
  3. [SOLVED] Using scripting dictionary to add contiguous group of items from non contiguous range
    By stnkynts in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2014, 06:12 PM
  4. [SOLVED] How to delete multiple contiguous (and/or) non-contiguous rows, in an excel table
    By jimmalk in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-03-2012, 05:48 AM
  5. Contiguous "Range" from Non Contiguous Ranges
    By DonkeyOte in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-03-2011, 03:23 PM
  6. contiguous reference
    By jjeff in forum Excel General
    Replies: 8
    Last Post: 08-25-2010, 10:47 AM
  7. Replies: 3
    Last Post: 03-03-2005, 02:06 PM

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