Flexible amout of rows in a table?

[Telgus]

Hi everybody,

I already got a lot of help with my excel files here, it’s a great forum!

I´m totally new at VBA, please keep that in mind

I have 4 tables (one for every quarter) that fetch information from a main table. Due to a probability-function (50%,70% or 85%) that I use, the tables could have 50 or only 5 rows. For example:
The main table has 50 projects in Q1 that have at least 50% probability of becoming an order. The Table for Q1 therefore needs at least 50 rows.

If I choose only to see the projects that have 85% probability of becoming an order I might see only 5projects in Q1. That means 45 rows could be shown empty, which would look a bit silly

Is it possible to have flexible tables depending on how many rows the table need in that moment (how many projects I choose to see)?
I added a file that might help to explain my situation a bit more.

Thanks a lot!

Best Regards,

Telgus

[LJMetzger]

Hi Telgus and welcome to ExcelForum,

If you change your design slightly, by using Excel 'Tables', you may be able to solve your problem.

See the download file associated with Post #5 in the following thread for working code that solves a similar problem: https://www.excelforum.com/excel-pro...in-tables.html

The example above uses several 'Tables' and there are CommandButtons to:
a. Add a Blank Row to a Table
b. Remove a Blank Row from a Table
c. Move Data up or down a row
d. Move Data between tables

Please let us know if you have questions or need additional help.

Lewis

[Telgus]

Hi LJ Metzger,

thank you for your response. I checked the Post #5 and found the code: Sub RemoveAllBlankLinesExceptOneBlankLineAtTheBottomOfTheSelectedTable().

Unfortunately I do not get it to work for my table. Is it possible to automate the code that the quater tables never show emtpy rows (ok with on empty at the end)?

Thanks you!

Best Regards,

Telgus

[LJMetzger]

Hi Telgus,

After reviewing your file in detail, I came to the conclusion that 'Data Tables' were probably not the correct approach for your application, because it probably would require too many changes to your Excel spreadsheet design. I apologize for leading you down the wrong path.

I came up with another solution that requires you to make ZERO changes to your file, subject to the following limitations:
a. Formula in Cell D1 is changed to: '=SUM(I1:I9999)' by both the VBA routines below.
b. Each Data Area requires at least 2 Data Rows to Maintain Totals Formulas.

See the attached modified copy of your file that contains the following two Macros in Ordinary Code Module ModManageDataArea:
a. RemoveAllBlankRowsFromCurrentDataArea()
b. AddBlankRowAtTheBottomOfCurrentDataArea()

The code in red below at the top of the Module can be modified to suit your needs if you change the Spreadsheet design.

Please Login or Register  to view this content.

Lewis

NOTE: Code Continued in next post due to size limitations

[LJMetzger]

Code continued

Please Login or Register  to view this content.

[Telgus]

Hi LJMetzger,

thank you so much for this help. I really appreciate it. I wish you a great day

Best,

Teglus