Forum Discussion

schlag58's avatar
schlag58
Copper Contributor
Sep 16, 2024

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

Excel
Macros and VBA
  • Hermes_Santos14's avatar
    Hermes_Santos14
    Copper Contributor
    Sep 17, 2024

    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

     

Resources