• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using macro to create a table

glennpc

Member
I have a workbook with 2 worksheets-- one is where I'm putting the final results I need to send to someone. The other is a sheet that I paste in a range to-- it has 18 columns and a variable number of rows. I then change that range to an Excel Table, add a TOTALS row, set some of the TOTALS to COUNT, and copy them from there into the final results worksheet. (not really a "copy"-- I use a formula on the final results sheet, to get those values individually and put them in the right place, then turn them from formulas to actual values).

The problem is that I used the macro recorder to create this. my table had 139 rows the first time I did it. But when I clear out the sheet and pasted in another range (with more or less rows), and create the table, it still creates a table with 139 rows. If my new table has less than 139 rows, I still get the correct numbers in the totals row, so I get the correct numbers on the final results sheet. But if I paste in a range with more than 139 rows, and then run my macro-- it still just creates a table with 139 rows, and I get wrong numbers in the total row (and on my final results sheet).

How can I make this work so it knows what size table to create from my range based on how many rows I have?
 
There are ways to check last used row and use that to convert range into table.

Something like below.
Code:
Dim lRow as Long
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

You then modify your Table range to something like .Range("A1:R" & lRow)

There are multiple ways to accomplish this. See link below for some examples.
http://www.globaliconnect.com/excel...ed-column-vba&option=com_content&view=article

http://www.thespreadsheetguru.com/b...to-find-the-last-row-or-last-column-using-vba
 
Thanks so much! This worked perfectly on my sheet and it wasn't a lot of coding! You guys at Chandoo rock! Thanks again.
 
Back
Top