Adding or deleting rows from Table depending of data

[colonel179]

Hello,

I got an excel file to calculate a credit payments. I input the number of months and all the credit data, and when pressing a button, it gives me every monthly payment in each row. If I choose 24 months, it gives me 24 rows of data for each monthly payment. If I then select 10 months, it automatically gives me only 10 rows of data.

However, the data doesn't have a good presentation because it's all in white cells. I would love to do a TABLE format with row colors and all, but I don't know how to make it so that the rows of the table are added or deleted depending of the data.

The most I was able to do was that if I choose 24 months, it add 24 rows of data, but then when I choose a lower value (8 months), the rows that were added stay there instead of adjusting to only 8 rows of data.

Is it possible to have the table adapt to the rows of data automatically? I would like it to expand or get smaller depending on how many rows are in use.

This also applies to formulas in which you have to drag down the formula to different rows to get values.

Thank you very much!!

[werafa]

Hi Clonel179

Tables automatically expand to include a row added at the bottom.
you can clear contents, or delete table rows
you can use the table filter buttons to filter the dataset
the subtotal function can work like sum - but can be set to only sum the visible (not hidden) data

I also believe that there is a built-in data entry form. it is available in the 'customise ribbon' menu (I've not tried it yet, so can't comment on how good it is)

referencing data in tables is a little different to normal excel

vba references to tables use the format myTable.databodyrange(rownum, colnum).value,
or myTable.databodyrange(rownum, mytable.columns("ColName").index).value

Your best bet is to try it - I think you will find most things logical, and it will do most of what you seem to be wanting.

Werafa