Data importing from another file with infinite blank rows

[RudeClown]

Good afternoon,

I have run into an issue I am in need of assistance with. I am importing data from 5 Excel spreadsheets into a sixth, however when it queries the first of the five files, it’s returning the data plus an infinite number of blank rows, up to the max and is not able to continue to pull the data from the other files. I have used the option in PowerQuery to remove blank rows, but it is still importing them. I have verified there is no data, and the formatting (drop-down boxes, cell borders, data validation) only goes down to row 25,000, so even if it was importing the rows with formatting, that would still only be 125,000 rows per file, not the millions it is trying to import. Any idea what might be causing that, and how I can remedy it? The files I am pulling data from will have new data added daily, with my intent being to refresh the 6^th “Master” spreadsheet to update it. Any assistance you can provide would be a great help!

 

[alansidman]

In each file and on each worksheet, click on the Control and End keys concurrently. This will show you exactly which is the last row and last column in your sheet containing some information whether it be data or formatting, etc. You can then remove any blank rows down to and including the one identified.

 

[RudeClown]

The ctrl+End showed me that each file went down to row 1.4 million something, although no idea why. But, I created a new sheet and copied the existing data over from the five to a new set of five sheets, which showed the ending point where I'd expect it to be. I did the import and it worked just fine. Thank you for the tip!

 

[Joe4]

Note this part of Alan's response:

This will show you exactly which is the last row and last column in your sheet containing some information whether it be data or formatting, etc.

It could be that they formatted the ENTIRE column initially, which could cause that behavior.