Blank Cells Getting Added

If I have data in all the catagories in my spreadsheet, when I update the worksheets everything works fine. If I have a catagory where there are no entrants the headers on my spreadsheets get written into with the word Value.

What should happen is that if there are no records to put in, then that worksheet should be left alone.

I have attached my spreadsheet and you will see that there are no entrants in the Novice Retriever or Open Retriever Catagories, pressing the update button will overwrite parts of the header.

Your question is rather simple compared to the code. I assume you used a lot of macro recording or took over the code from someone else. No harm in that, I just want to know what skill level you are at.

I haven't tested anything but here is what looks wrong to me:
The loop on the Entrants sheet starts on lrow=2 but the data range start at row 8.
On the other sheets it also starts with lrow=2 but the data range starts at row 3.

I strongly recommend you to tick the box for "Require Variable Declaration" under "Options".
Type "Option Explicit" on the top of all your existing code modules. This will force you to declare variables, making the code so much easier to read and to debug.

Very well! I'm done for tonight but hopefully I can take a look at it tomorrow. I just did a quick test of changing 2 to 3 but it didn't seem to make any difference so there is probably more to it. Shouldn't be that hard though.

I took a more thorough look at it now. The code is pretty clean but it could certainly be improved. I didn't do that, I just did the minimum to make it work and I think it works now.


Yes the 3 should be changed to an 8, like you already done.
I also changed a few lines of code as you can see in my comments in the code.
The code for the Update All button seems to have been copied for the individual Update buttons since there is a loop looping through all sheets in each sub. I simply blocked out the loop with ' and put in a name for the worksheet instead of the "i".

I also added an IF to test if the sheet is empty.
If the sheet is empty lrow becomes 2 and all the formulas like
(.Range("E3:E" & lrow).FormulaR1C1 = "=SUM(RC[-1]/20)")
will actually go from E3:E2. That's where the problem is since you have your headers on row 2.

Now, I'm a bit lazy so I was hoping you could make the changes I did for "Spaniel Special Puppy" on the other dog classes as well.
No need to mess with the copy_rows() sub, it should be done.

Please Login or Register  to view this content.

Superb, many thanks for the work that you have done!