Automatically Adjusting Rows in a Range

[schlag58]

I run a daily golf league where the number of players is not consistent.  The data is removed after every use.  I use multiple tables that randomly creates teams using RAND function.  It also sorts and rank player performance and sum winnings from 3 tables (team performance, individual performance and skins).  Every time I use the workbook, I need to manually adjust the table sizes in multiple pages since any #N/A or #VALUE! in a table will interfere with sum, match, vlookup, sorting and rank functions.  Is there a way to automatically adjust the number of rows in all of the tables based on the the number of players (PLAYER COUNT)?

[#2]

Hi @schlag58

Here are some possible solutions I can think of at the moment. You can try adapting one of these options to your case.

Option 1: Use Dynamic Formulas with the IFERROR Function

To avoid values like #N/A or #VALUE! from interfering with your formulas, you can use the IFERROR function. This allows the formula to continue working even if an error occurs.

Example:

=IFERROR(VLOOKUP(A2, Players_Table, 2, FALSE), "")

Here, the IFERROR function ensures that if the VLOOKUP generates an error, Excel will return a blank cell, preventing a negative impact on other formulas.

Option 2: Control the Number of Players with Formulas

Create a cell to automatically count the number of players present, for example:

=COUNTA(A2:A100)

 Here A2:A100 would be the range where the player names are listed.

Now, use this value to adjust your ranking and summing formulas. Combine this count with formulas that avoid processing empty or erroneous cells.

Option 3: Filter Valid Rows

Use the FILTER function (if available in your version of Excel) to create a dynamic range that automatically excludes cells with errors:

This example filters all rows that have valid (numeric) values in column A.

=FILTER(A2:B100, ISNUMBER(A2:A100))

Option 4: Using VBA

If you want to fully automate the table updates based on the number of players, you can use a small VBA script that checks the player count and adjusts the tables accordingly.

Example:

Sub AdjustTables()
    Dim lastRow As Long
    ' Assuming the player names are in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Here you can adjust the table you want to resize dynamically
    Range("Players_Table").Resize(lastRow, 2).Sort Key1:=Range("A2"), Order1:=xlAscending
End Sub

[#3]

@Hermes_Santos14 

Thank You!  These are solutions I had not thought of....I will give them a try!