Summary Sheet Code creating additional blank rows

[ghostly1]

Good morning All

I have a workbook that I created which loops through each sheet and extracts my required data and places on the front summary sheet. I do apologise as the code I have written is primitive and laborious (happy to receive a cleaner simplified version :-) ).

Anyway the workbook operates almost as I require, but poses the following issues:

1. Upon adding the next sheet details the code keeps adding additional blank rows which increase incrementally. I believe this is something to do with the 'Counter +1' command but for the life of me I cannot resolve without messing up the sheet

2. If I have a worksheet that only have one entry in it, the code falls over (feel free to copy a sheet, rename it and only have one line of data and run the code to see this error). I am not skilled enough to resolve and have already spent 3 hours on this with no avail.

Here is my code (plus workbook attached)

Please Login or Register  to view this content.

Please could somebody assist?

Many thanks
G1

[sintek]

Rule No 1 in excel....Avoid merged cells...Just cause havoc...

Anyway, this solves...

Please Login or Register  to view this content.

[ghostly1]

Good afternoon Sintek

OH MY WORD, How did you to that so quickly and your code runs in a fraction of the time mine did. Thank you so much as it does everything I was looking for. The fear I had with mine was that as I add more sheets, the code will run slower and slower, but yours appears to not have that issue, so thank you so much. I have been using Excel for many years but as I have never really concentrated in VBA, I have always stayed at the same novice (code stealing) level. I need to invest time to learn as although technology moves on, everyone needs spreadsheets so will give job longevity I am sure :-).

May I ask why merge cells is such an issue? Just means that I will need to manually merge cells on the summary, but considering the time your code has saved me, it's a small price to pay.

Many thanks again Sintek
Magic

[sintek]

How did you to that so quickly

Had a similar request a couple of days ago...

why merge cells is such an issue?

Just causes havoc...There are alternatives that have the same effect...
https://www.excelsupersite.com/do-yo...to-merge-that/
https://edu.gcfglobal.org/en/excel-t...rging-cells/1/
https://accessanalytic.com.au/stop-merging-cells/

Thanks for Rep...