+ Reply to Thread
Results 1 to 8 of 8

Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

  1. #1
    Registered User
    Join Date
    07-06-2016
    Location
    ARRA BROOKHAVEN
    MS-Off Ver
    2013
    Posts
    9

    Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Hi,

    I have a workbook set up with various sheets for individual contract managers. On each sheet there is a table listing all contracts that are due for review. I have the following code which enables me to email a copy of each table to the relevant contract manager.

    Please Login or Register  to view this content.
    At present this code sends everything from row 5 to row 100 - however I do not need it to send 100 rows as the number of rows will vary from month to month when I run the reports.

    Is there an alternative to; ActiveSheet.Range("C5:N100").Select that will select all rows that have data in them, instead of all rows up to 100?

    Many thaks

    Ross

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Hello Ross
    You can find the last used row of a column, for example this finds the last used row in column C:

    Please Login or Register  to view this content.
    If the last used row in column C was for example, 16, this would select C5:N16.

    DBY

  3. #3
    Registered User
    Join Date
    07-06-2016
    Location
    ARRA BROOKHAVEN
    MS-Off Ver
    2013
    Posts
    9

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Hi DBY - thanks very much for your help

    I have another idea I'd like to try, instead of starting a new thread I thought I'd ask here seeing as it relates to my initial question.

    As above, I' trying to email tables to individual employees. Initially I had individual tables on individual sheets for each employee, and had set up a macro to email the entire table to each employee individually. I've now decided to run one main table with all employees listed. I'd still like to email the table to each employee, however I'd only like to email the table rows that contain their name.

    This is the code I initially used to send the entire table to each employee.

    Please Login or Register  to view this content.

    The code I was using to populate each individual table based on employee names is;

    Please Login or Register  to view this content.
    The above code works perfectly for populating each individual table - with each employee's sheet having a variation of the above code to reflect their own name - i.e. "Andre Marques".

    Is there anyway of incorporating the above code into the code I'm using to email the table, so that the email only send rows of the table that contain specific employee names, for example, Andre Marques.

    I hope the above makes sense

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Hello
    If each employee's data is grouped together in the single table then it's quite straightforward to find the first and last row of that employee' s range to select. Can you upload some non confidential sample data to work with?

  5. #5
    Registered User
    Join Date
    07-06-2016
    Location
    ARRA BROOKHAVEN
    MS-Off Ver
    2013
    Posts
    9

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Hi DBY,

    I've replaced any confidential info and have attached the file

    If you look at the VBA code you'll see that I have it set up to send the entire table to a recipient.


    What I'd really like to do is, instead of sending the entire table to each employee, I'd like to send only the rows where there name falls into column 'F' (Contract Manager). Ideally I'd like to be able to run the code once and it send an email to each Dan Smith, Andre Marques, Ben Acme, Danny etc. that contains only the rows of data where there name falls into under the 'Contracts Manager' column.

    I hope this makes sense!

    I should also mention that when the rows are sent via email,it should keep the headers in place so that the employees know what each column relates to.


    Thanks again!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Hello
    There's a couple of ways to do this. What format do you want the copied range in? As the Table format or plain with borders and so on?

  7. #7
    Registered User
    Join Date
    07-06-2016
    Location
    ARRA BROOKHAVEN
    MS-Off Ver
    2013
    Posts
    9

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    Ideally I wanted the copied range to be in the same format as the original table - if possible

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Make 'ActiveSheet.Range("C5:N100").Select' - select a dynamic range of cells

    The following macro selects the chosen Contract Manager's records in Table1:

    Please Login or Register  to view this content.
    Could you incorporate something like this into your email code?

    Sorry it took awhile to get back I haven't been able to access the forum.

+ 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. .select method with worksheets object ex. Sheets("Sheet1").range.select fail
    By RobCan in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-19-2014, 07:01 PM
  2. range("e1" & cells(rows.Count,5).end(xlup)).Select
    By sohaila in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2014, 09:27 AM
  3. Replies: 3
    Last Post: 03-08-2013, 09:28 AM
  4. Help with Macro: Using "record", how to select a dynamic range of cells
    By Stalinski in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2012, 06:04 PM
  5. select a range using "cells()"
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 12:05 PM
  6. [SOLVED] select a range using "cells()"
    By fullers in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 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