How BA updates the Excel sheet

[mellenec]

When, after a race (with 3 horses for example) has been selected, BA updates the Excel sheet, and you can see, while stepping through the Sheet change event that the 3 horses have been displayed in the sheet, but the last race horses( if > 3) are still there and will be deleted only after the event is left.

This seems strange to me...
How can I pick the correct number of horses (3 in this case) I need to copy with some other infos into another sheet?

[Mitch]

The way I do it is to store the race name in a constant variable, then when A1 is not the same as the variable, clear all the cells.

[mellenec]

The way I do it is to store the race name in a constant variable, then when A1 is not the same as the variable, clear all the cells.

Yes , I know how to do that ( I have the code in the Sheet Change event), but

- where do you put the code to do that ?
- how would you get the number of horses ? ( doing this in the Sheet Change event won't work, as I explained in another topic about how the Sheet is updated by BA)

[Mitch]

I have the code in the sheet change event if it's a small macro, or in a module called by the sheet change event if it's a big project.

I use the last row in col F minus 4 for the number of horses. It doesn't matter to me if there are non-runners. You could use COUNTA in your code if you only wanted to know how many horse names there were in col A.

[mellenec]

I have the code in the sheet change event if it's a small macro, or in a module called by the sheet change event if it's a big project.

I use the last row in col F minus 4 for the number of horses. It doesn't matter to me if there are non-runners. You could use COUNTA in your code if you only wanted to know how many horse names there were in col A.

Hi Mitch,
I finally fixed my problem:
If you do that without precaution, what you get the first time the sheet Change event is executed is the number of horses... of the last race, and until you leave this first Change Event subroutine, you may have some of the last race horses displayed (should they outnumber the current one's)!
It is only in the next Change events (assuming the race is the same one) that you get the right number and and with it you can delete the extra horses.

It's only after some debugging that I became aware of this.