Macro to add table to email from excel. (stop at last row input)

[Luke1690]

Hi All,

Im using a Macro to copy a table from excel and put it in an email. Everything works but i can seem to get the ranges right. i want it to only copy up to the last row data input from A3 Down.

count_row = WorksheetFunction.CountA(Range("a3", Range("k3").End(xlDown)))
count_col = WorksheetFunction.CountA(Range("a3", Range("k3").End(xlToRight)))

Set pop = Sheets("tracking").Range(Cells(3, 1), Cells(count_row, count_col))

My data is in A3:K3 with them being the titles.

how do i get it to only copy up to the last input row in column A. as some of the other columns my still be blank up to K.

i keep messing around with the copied code above but getting nowhere!

Thank Guys.

 

[Joe4]

If you want to find the last row in column A with data, just do thos:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

 

[Luke1690]

Thanks Joe,

i know how to find it using that. how do i put that into the below.

count_row = WorksheetFunction.CountA(Range("a3", Range("k3").End(xlDown)))
count_col = WorksheetFunction.CountA(Range("a3", Range("k3").End(xlToRight)))

Set pop = Sheets("tracking").Range(Cells(3, 1), Cells(count_row, count_col))

Ranges in red are wrong, just cant figure out it out.

this bit of the code is part of ensuring it copies as a table and not just text on the email.

 

[Joe4]

If you are trying to copy the full contents of a table that starts in cell A3, why not just use Current Region, i.e.

Set pop = Sheets("tracking").Range("A3").CurrentRegion

 

[Luke1690]

If you are trying to copy the full contents of a table that starts in cell A3, why not just use Current Region, i.e.

Set pop = Sheets("tracking").Range("A3").CurrentRegion

Sorry Joe,

so my table starts from A3 and goes down to 1000. i have a macro set up to filter certain data, from that macro you can select to copy that filtered table into an email.

The column to always have date in it as some of the cells will be blank, will be (A) I want copy across from the last data entry in A to k that will be that table.

im a novice Joe, i found the code online.

the code ive put and .currentregion copies all of the table up to row 1000 and puts it on the email

 

[Joe4]

OK, that is the first you mentioned of a Filter being used (kind of an important detail!)
What happened when you tried my last suggestion.

Before I go any further, I think it would be helpful to see some sample data and expected results, to make sure we have ALL the necessary information.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Luke1690]

OK, that is the first you mentioned of a Filter being used (kind of an important detail!)
What happened when you tried my last suggestion.

Before I go any further, I think it would be helpful to see some sample data and expected results, to make sure we have ALL the necessary information.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

it copied the data i needed but also all of the blank cells past that. (it was a big table on the email)

i will see what I can do, there is a a lot of data on the sheet and in the code i will need to hide.

 

[Luke1690]

count_row = WorksheetFunction.CountA(Range("a1", Range("a3").End(xlDown)))
count_col = WorksheetFunction.CountA(Range("a3", Range("a3").End(xlToRight)))

Set pop = Sheets("tracking").Range(Cells(1, 1), Cells(count_row, count_col))

these worked with these ranges. dont ask me how.

 

[Joe4]

Glad it worked out for you.

To be honest, I was a little fuzzy on what exactly you were trying to do, in relation to your data (which we never were able to see).
I thought I had a general idea, but didn't even know you were filtering data at first, until you mentioned it in a later post.
But so much actually depends on the structure of the data, and desired result, which is why it is so helpful for us to see that.

Remember, while you have all that in front of you, and know exactly what you are trying to accomplish, we don't have that luxury. All that we have to go on is what you share with us.
And visual aids are often very helpful in describing these type of situations more clearly and completely.

 

[Luke1690]

Glad it worked out for you.

To be honest, I was a little fuzzy on what exactly you were trying to do, in relation to your data (which we never were able to see).
I thought I had a general idea, but didn't even know you were filtering data at first, until you mentioned it in a later post.
But so much actually depends on the structure of the data, and desired result, which is why it is so helpful for us to see that.

Remember, while you have all that in front of you, and know exactly what you are trying to accomplish, we don't have that luxury. All that we have to go on is what you share with us.
And visual aids are often very helpful in describing these type of situations more clearly and completely.

Thanks Joe,

Next time i will try and send a sample.