Set Table Size?

Try event code like this - copy the code, right-click the sheet tab, select "View code", and paste in the window that appears.

Please Login or Register  to view this content.

Well, the code is only applied when the row count of Table1 increases above 20. So grab the table size handle and drag down, and the once you get more than 20 rows, the rows should change height. If you want to see it in action with all row counts, just use this version:

Please Login or Register  to view this content.

Well, the code is only applied when the row count of Table1 increases above 20. So grab the table size handle and drag down, and the once you get more than 20 rows, the rows should change height. If you want to see it in action with all row counts, just use this version:

Please Login or Register  to view this content.

Thanks Bernie!

The original code does the trick. I just needed to adjust the amount of rows and 240 is actually the perfect height. How crazy is that? lol

I appreciate the help

240 is always the answer - you just need to adjust the units to suit

Sorry, that was an old engineering joke. Say, for example, the real answer is 24 inches. Then you give the answer as 240 but with the units tenths-of-an-inch. Any number can be expressed as 240 if the units are adjusted appropriately.... a related engineering standby is the "<insert name of the smartest kid in the class here> constant", which is how much you need to add to, subtract from, mutliply, or divide your answer by to get the correct answer.

Anyway, to change the bottom row to a specific height, use code like this

Please Login or Register  to view this content.

Thanks again,

I'm sorry to keep dragging this out but I thought the code would make more sense to me so I could manipulate it a little myself. But that isn't the case lol. What is iTR? Is it just a label you made up to signify "Integer Totals Row"? How does the code know that you're applying this to the last row in the table? Do I need to label the bottom row as "Totals"? The last line before End Sub looks like you're applying the Totals Row height to the last row but I can't see how you're referencing the last row specifically.

The reason I wanted to manipulate it myself was because I realized that I want to adjust the top row as well. However it is that you're applying the height to the bottom row, I imagine I need to do that for the top row as well then simply change the count to "2" instead of "1".

I think what's really got me confused is the line below. I'm reading it as ListObjects "Table 1" the range of the table to apply the command to is, ListObjects "Table 1" the range is all cells. Maybe the problem is I don't know what "count" is. It seems like it is a command to count (der... lol) but I don't see how that applies here.

Please Login or Register  to view this content.

Again, I'm sorry for dragging this out. You've already been a big help, I just end up wanting to make further tweaks after I see results sometimes and understanding how the code works will help me do that.

Let's break this down:

ListObjects("Table1").Range(ListObjects("Table1").Range.Cells.Count).EntireRow.RowHeight = iTR

ListObjects("Table1") is the table
ListObjects("Table1").Range are the cells of the table
ListObjects("Table1").Range.Cells.Count is how many cells there are in the table
ListObjects("Table1").Range(ListObjects("Table1").Range.Cells.Count) is the bottom right (last cell) of the table

EntireRow means the entire row of the range
RowHeight is the row height for a range that is a row

and iTR is the height we want for that row.

We can use Range(1) since we know the headers are always in the top row, and cell 1 is the top left cell of the table.

So, all together, this

ListObjects("Table1").Range(ListObjects("Table1").Range.Cells.Count).EntireRow.RowHeight = iTR

can be read as "In Table1, set the last cell's entire row height to the value stored in the variable iTR"

We have to set the entire row's height since height is not a property of a table object. You don't need to label that row, as long as your table is set up to have totals as the last row.

So, for the first row:

Dim iHR As Integer 'integer header row height
iHR = 30
ListObjects("Table1").Range(1).EntireRow.RowHeight = iHR

(Note that you need to adjust the height that is being shared by the other rows: 240-iTR-iHR, and subtract 2 instead of 1 in the divisor

We can use Range(1) because the headers are always in the top row, and cell 1 is the top left cell of the table.

But - if you ever want to write a macro that is not event code, you need to fully qualify the ListObject: event code applies to objects on that sheet, so qualification is not needed:

Woksheets("Sheet Name").ListObjects("Table1").Range(1).EntireRow.RowHeight = iHR

Easy....

Please Login or Register  to view this content.