+ Reply to Thread
Results 1 to 10 of 10

Auto select varying range of cells that may change size for VBA mail code

  1. #1
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Auto select varying range of cells that may change size for VBA mail code

    I have VBA code to take a selection of cells, open an email, then paste it into the body. The challenge I've been faced with now is sometimes we will need to add additional information each time we use this template, so the number of rows in the range of cells may change. Sometimes it will be more, other times will just be the original number of rows in the template.

    How do I get VBA to select only the range of cells that have information whether it be 6, 12, or 18 rows.
    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Re: Auto select varying range of cells that may change size for VBA mail code

    Oh, the range will always start A3, it's the value for B that could increase.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Auto select varying range of cells that may change size for VBA mail code

    Perhaps.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Re: Auto select varying range of cells that may change size for VBA mail code

    I get a compile error: Invalid or unqualified reference and it highlights .range just before ("B".

  5. #5
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Re: Auto select varying range of cells that may change size for VBA mail code

    Nevermind... Got it to work... Thanks! Glad it was a simple solution.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Auto select varying range of cells that may change size for VBA mail code

    Was there a mistake/error in what I posted?

  7. #7
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Re: Auto select varying range of cells that may change size for VBA mail code

    No, no error, the fault was my own. I didn't set it up as a "With" statement. I just used the setrng without it not realizing it was a with statement. When I got the compile error, I went back and saw what you put. I changed my code to that, and it worked, but now I have another question. I basically want to do the same thing, however this time I have 3 different sets of ranges I want to use, 2 on one worksheet and one being on a different worksheet all in the same workbook. These ranges will always be constant, so I tried the code below, but obviously it didn't work:

    Please Login or Register  to view this content.
    I didn't use the with function since the ranges weren't going to change size. Is this a simple fix too?

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Auto select varying range of cells that may change size for VBA mail code

    What are you trying to do with that code?

    Are you trying to combine all 3 ranges and have them sent as the body of the email?

  9. #9
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Re: Auto select varying range of cells that may change size for VBA mail code

    Yes, one right under the other one.

  10. #10
    Registered User
    Join Date
    09-12-2014
    Location
    Morrisville, NC
    MS-Off Ver
    2013
    Posts
    40

    Re: Auto select varying range of cells that may change size for VBA mail code

    Anyone? Anyone? Bueller? Bueller?

+ 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. Change font size of text in mail VBA code
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-05-2013, 12:51 PM
  2. [SOLVED] Using code to select a cell directly below a range of an unknown size
    By LaffyAffy13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2013, 11:44 AM
  3. [SOLVED] How to select and delete a range of cells between two different ranges of unequal size?
    By herbie226 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2012, 04:40 PM
  4. [SOLVED] Dynamic Range Reference, Varying Size of Range
    By cdiaz in forum Excel General
    Replies: 2
    Last Post: 06-30-2011, 02:49 PM
  5. [SOLVED] Subtotals in a range of varying size
    By CEG in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2006, 01:55 PM
  6. Using Varying Range Size in Macro
    By Charles Allen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2005, 08:06 PM
  7. sumif range of varying size
    By KM01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2005, 06:06 AM

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