Retreiving Data from workbook, but rows can be added or removed.

You have Excel 2016. Use PowerQuery (called Get & Transform) to pull in the data. It will be dynamic. You can then use GUI or M query language to transform data as you see fit.

Make your range dynamic and use a variable to hold the range parmeters. Use the variable throughout the code instead of the hard coded range parameters.

Please Login or Register  to view this content.

Then it will automatically adjust the range regardless of the adding or deletion of rows.

The issue is different than I first envisioned. What you are apparently trying to do is make the code idiot proof. That could take a lot of If...Then statements to cover all aspects of what a user might do. I think it might be easier to make sure your users understand the limitations. You can put a condition in the code for the unexpected blank row by having a message box pop up to tell the user that no data was found and which workbook the problem occurred in so they can take remedial action. then allow the code to continue for any remaining workbooks. Flagging the anomally is easier than trying ot out guess the user.

An alternative would be to make sure the source workbook has data in the appropriate range before it is saved.