Counting number of non blank cells and inserting that amount on another sheet

Hi All

I've been working on a bit of code to do as the title suggests, have tried a variety of things, but they all seem to be having the same affect.

The basic overview is that throughout the month, a colleague will be inputting people to this system, and at the end of the month the data will be sent to our accounts department.

The accounts team need a very specific form, which is fine and is set up - but by default I only want it to have a couple of lines where the employee details are entered, then when the macro is ran, I want it to count how many entries have been input throughout the month from the data capture sheet, and insert this amount of rows into the accounts sheet.

I so far have the below code:

Please Login or Register  to view this content.

The code is unfinished at the end, as you may be able to see once inputting the rows, it goes back and copies the field, then inputs - this bit is fine, I just haven't got past this obstacle yet.

The problem is, whilst the above affectively does what I want it to, it's inputting the wrong amount of lines. I have added and removed data from the Data Capture form to test, and regardless of there being 3 or 9 cells in "A" with data in, it inputs 19 rows each time!

Anyone able to help with this at all? I have checked the cells and there is no hidden formula's/text or anything in them.

Maybe you could try something along the lines of:

Please Login or Register  to view this content.

?

Hi Yudlugar

That seems to work, but now encountering a bit of a problem with the formatting of the entered cells.

Below is before and after running macro

Before

before.png

After

after.png

Is there something I should possibly use other than

Please Login or Register  to view this content.

Thank you though for previous response - has got me a bit closer to the end of the tunnel =)

Please can you upload your workbook with the full code, I can't tell at the moment.

Hi

Please see attached - thank you =)

I'm not 100% sure it's what you want it to do but I think what you are looking for is:

Please Login or Register  to view this content.

PERFECT. Thank you very much! The only issue I have now is that where I have 3 rows already there, it has 3 too many at the end, however as this is always going to be the case, changing the -2 at the end to -5 should fix this issue.

Thanks again!

I'd try something like:

Please Login or Register  to view this content.

That's spot on - I did notice my -5 didn't work, it just messed up the formatting - your way does it great!