Find Last Row Containing Data (DO NOT count blank rows at the end)

[ryanmorris]

This is by far the most infuriating issue I've run into guys.

I have 200 workbooks that I need to open and copy data from (pasting into one destination workbook). To do this, I am trying to determine the last row of data in each workbook during the copy & paste procedure. However Excel is arbitrarily adding blank rows at the end of the data into the count.

For example, the first workbook has 23 rows of data. Excel determines that the last row of data is row # 23. It copies this, then pastes it into the destination workbook. In the destination work book, when I try to add formulas in the last column of the data I tell it to run a formula and fill down to the last row of data in the destination sheet. Now clearly, there are only 23 rows but Excel is pasting the formula down to row 65.

I have searched through two pages of Google search results and the search function for this forum is returning zero threads. The current code I've tried most recently is as follows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you all in advance!

[JapanDave]

Why not the good old fashion,

Please Login or Register  to view this content.

[ryanmorris]

Thanks JapanDave, still having the same problems though.

[ryanmorris]

Okay, so I finally fixed it with the help of a colleague. I've included my code so that it can help anyone going forward. JapanDave, I did change my counting lines to the ones you suggested so thank you again.

Please Login or Register  to view this content.