Mar 20 2018 07:10 AM - edited Mar 20 2018 09:42 AM
When I highlight the cells I want to autofill down and double click the autofill handle, it fills past the last row of data on the left. I have verified through =ISBLANK() and even deleting the blank rows and redid the autofill that this continues to be a problem. See the attached images of the Beginning and End Result. Is there something that I am missing. I have never run in to this issue before with autofill.
Issue is in AutoFill-ing down formulas in O2:S2.
Mar 20 2018 09:37 AM - edited Mar 20 2018 09:38 AM
Erin,
Could you please provide us with the workbook itself?
The screenshots are not much helpful!
But I guess that there are some zero-length strings in the worksheet.
Regards
Mar 20 2018 09:43 AM
I have amended the original post to include the workbook. The issue is on the Master tab in copying down the formulas in O2:S2 via highlighting and double clicking the handle in the bottom right corner to autofill down.
Mar 20 2018 09:54 AM
Hi Erin,
Your cells under the bottom of the data are not clean (you may check by Ctrl+End). I made Inquire->Clean Excess Cells Formatting, after that autofill works correctly.
Mar 20 2018 10:20 AM
Erin,
In fact, I didn't find the reason for this issue!
The area around the data is totally blank!
But I've done this, and the problem disappeared!
Hope that helps
Haytham
Mar 21 2018 04:25 AM
Thanks for this. Do you know if there is any way to see in Excel what this formatting is that it is cleaning up? I am curious what is putting it there so that I can adjust as necessary so it won't be there in the first place.
Thanks,
Erin
Mar 21 2018 04:25 AM
Thanks, I had tried deleting the rows but not the columns to the right. Apparently that makes a difference.
Mar 21 2018 06:57 AM
Hi Erin,
I'm not sure how to recognize that visually. It happens if you have some data, especially in formatted columns, after that remove them. More about that is here
https://support.microsoft.com/en-gb/help/244435/how-to-reset-the-last-cell-in-excel
Mar 04 2019 11:48 AM
I have the same problem, but the Inquire/Clean Excess Cell Formatting doesn't help. Can't see anything from D42 down, nor F42 down. But if you select E25 and double click to fill down (only wanting to go thru row 41), it will autofill down to row 2001. ?
Mar 04 2019 01:36 PM
Down from row 42 your cells are not clean - the have some formatting, formulas, etc. If you Home->Clear->All starting from row 42 down to end, when autofill will work from E25 to E41