## Forum Discussion

# Automatically Adjusting Rows in a Range

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)?

- Hermes_Santos14Copper Contributor
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`

- schlag58Copper Contributor