VBA to resize a table based on a cell value

[Vasool]

Win7 64 bit, and Excel 2013 user here.

Hi, I'm fairly decent with regular excel programming, but I know next to nothing about VBA other than how to get to where you view the code.

I have a table named Table504

Elsewhere on my sheet in cell E7 is a value that changes. i.e. it might be 5 or 4 or any whole number.

I inserted a button onto my sheet.

I would like the onclick event of the button to resize Table504 to where the rows are the same as the value of cell E7. So if E7 has a value of 4, I would like Table504 to have the header row, plus 4 rows in its field where I have equations, and then I still want my Totals row at the bottom for subtotaling everything in the table. I don't want to delete any of the contents of the cells in my table, I just want to resize it, so that it would be almost an equivalent to using the filter and selecting to not show blank rows. (They aren't really blank, they just have formulas which result in a "" answer.)

Please help, and thank you.

[ajryan88]

Hi,

Try this subroutine:

Please Login or Register  to view this content.

Just alter the strings at the top of the code to match the location of your data/table.

Hope this helps

[Vasool]

Thank you!

I had to save it and close the workbook and reopen the workbook before it started working, but the code works like a dream, exactly what I wanted!

[ajryan88]

No problem!

Please don't forget to click on the * next to my post to say thanks