Update VBA code - remove blank rows in newly created sheets

Hi friends,

I would like to kindly ask you for help with updating of VBA code in attached example spreadsheet.

Let me simple clarify what this file is doing and what I need to update.

1. In sheet Analysis are data with workers (column "B") & account groups(column "C")
2. Once you click on button CREATE LOI you will be able to choose one or more workers for which you want to create new sheet(s)
3. New sheets are created from default tab LOI 4.0 and fill in by appropriate data from Analysis for each worker/account group's combination
4. In final you can see new sheet(s) called per account group (e.q. CN-XX-XXXX-250X0X-PX0XX)
5. In new sheets was data pasted in range between rows 17-155.
6. At the end you will see the huge number of blank rows in created sheets.


My request:
Could you pls help me to update VBA code in attached example file, which ensure, that blank lines up to row 155 in every newly created sheet will be automatically deleted?

I suppose below code used in attached file should be updated, but I'm not sure:

Please Login or Register  to view this content.

Thank you very much for your help

I added my effort beneath what you have, rather than copying your entire original code. You can relocate the dims if you like cleaner code. It works for me, though getting it to work on 'Dave' - who has two account groups and thus creates two new sheets - required a loop through the sheets. The danger there is if you run the 'Create LOI' function for a worker, then immediately run it again for another worker , the loop created by the second trip through will do some unwanted deleting on the first created sheet. My contribution below therefore works as requested, but you'll need to relocate each new sheet to another workbook before running it again.

If it's an option, I strongly recommend hiding the unused rows between 17 & 155 rather than deleting them. Hiding the rows would avoid the complication described above, allowing you to use 'Create LOI' consecutively.

Please Login or Register  to view this content.

Hi cantosh, thanks a lot for your answer. I agree with you, that use "hide" option is much better for my purpose. Your update fully reach my expectation. Big thanks, man!!!!!

My Pleasure! Don't forget to mark the thread as 'Solved' if you're all set.

Sure, already did it.