Re rating columns

Good Morning,

I have an interest in horse racing and I use spreadsheets to help with my form study. I have reasonble knowledge generally of excel but am self taught.

What I want to be able to do automatically is to re rate 6 columns (shown with yellow at the top)(columns BA-BF)in a worksheet after scratching occur. I have away of eliminating the ratings out of the 6 columns after scratchings but would like to rerate the columns so the ratings start at 1 and rate up. Now when a zero appears it needs to stay a zero as that means it has no rating but where a blank occurs it would indicate a scratched horse. I want to keep the number of rows without deleting any for the scratched horses so it matches other worksheets I use and also need to keep the horses in race order.Hope this makes sense.

Any pointers or help would be fantastic.

Thanks
Brett