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

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

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

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?

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!

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?

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

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.