Okay, so Excel freezes when I attempt to capture the mini sheet with that Add-In. Sorry about that. So, I'll do my best with pasted images.
This workbook is essentially an Auction Team Fantasy Draft, where eight players bid on a randomly selected college basketball team for March Madness.
There are EIGHT "Players," 64 "Teams," and 64 "Picks" in this Draft.
The important cell above is the merged AE3 where it says "Donny." In that cell is a lookup (=XLOOKUP($AA$3,'V TEAMS'!$X$24:$X$143,'V TEAMS'!$W$24:$W$143)) that pulls from another sheet ("V Teams") based on the Pick # in Cell AA3. The spin button next to AA3 increments by one when clicked, and it's the next person's turn. We then draft teams in 'snake draft' order (1, 2, 3...8, 8, 7, 6...1, etc.). Since this is an "auction" format league, any player can bid on that team, so in theory, Donny may not pick USC. It may go to Player 2 or Player 7, whomever wants to bid the most. Once each player reaches EIGHT teams that they've bid on, they will no longer pick. My macro that we were discussing on this thread is supposed to find all instances of the maxed out player's name from the range "VTEAMS!$W$24:$W$143" and delete and shift up, so that this player will no longer show up in cell AE3. I can't, however, simply skip to the next Pick #, because someone will need to bid on that next team in succession. If we skip it, then we've skipped a team. So, I need to skip only the player's name, not the pick or the team.
The above image is on the same tab, located directly below the first image I pasted. Ignore the purple "RANDOM" section, as that is out of play here. But within the green "AUCTION" section (rows 23-30), this is where the team name that's currently being auctioned off will be placed once that player bids on the team (FYI, there is another set of these below with the remaining four players - irrelevant for what I need here). I've placed mock teams and bid amounts in Player One (Donny)'s green section (Column J, Rows 23-29), as though he has bid on those teams, in order to test this macro. Notice in cell J32 where it shows $2 remaining. This is the amount that Donny has to bid on his remaining team, once his final team shows up in cell AE3 above. I would then enter that team in cell B30 and $2 in J30. This would trigger a COUNT function on the V TEAMS tab to equal 8. This is when I want my automation to occur. Ideally, I would like for Donny's name to disappear from the list of players so that he is now skipped over.
The above image includes the V TEAMS tab range that I'm attempting to delete and shift up (W24:W143). Notice cell Z13. This is where Donny's "team count" is at SEVEN currently. Once this reaches EIGHT, my macro should be triggered, and all instances of Donny's name should be found in W24:W143, deleted, and those cells only, shifted up. X and Y need to remain as is. If this happens successfully, all other players move up one pick, and Donny is removed. This of course will happen with each player eventually throughout the draft.
So, referencing the Autofilter macro that you suggested, as I stated, this macro does what it needs to do, however, I'm having trouble figuring out how to initiate this macro once cell Z13 (in this 'Donny' example') reaches 8. Obviously, each of Z13:Z20 will need to initiate the macro for that player.
