xlDown Macro copying 1 million+ blank cells

[KGEORGE13]

hello,

I have a database workbook with about 10 pages of VBA code. The purpose of most of the codes is to filter through a table and find unique parts and paste them into another table. I use the "xlDown" code a lot when copying the given range of the table needed to copy, but I've come to find out that when the code finds no end, it pastes 1 million cells.

Is there any easy fix in the VBA code for this?

Thank you!

 

[Joe4]

Yes, better to start from bottom and use xlUp instead, i.e.

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

The only situation in which that would not work is if the very last possible row in that column is populated (row 1048579 for xlsx files).

 

[JoeMo]

Do you want your copy range to stop at the last filled cell in a column? If yes, maybe something like this:

Dim LastRow as long
LastRow = Range("A" & Rows.count).end(xlUp).Row

 

[KGEORGE13]

Thank you!

But, now it is not returning anything. My sheet was working great with xlDown, the only problem is it is a million rows long and taking up a lot of memory. Is there anyway I can keep the same VBA code with xldown and delete all the blank rows?

 

[Joe4]

But, now it is not returning anything.

Please post the relevant section of code so we can see how you have applied it.

Also, on the pages where there are problems, what exactly does the data look like in the column where you are using the xlDown?
Are there any blanks in the middle of the data in that column?
Where is the last row of data in that column?
Are there any blank cells with formulas after the last row of data?

 

[KGEORGE13]

Range("A1").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Selection.Copy
Range("O14").Select
ActiveSheet.Paste

I am trying to move filtered data from a table and paste it on the same sheet. But each filter has a different amount of rows, so that is where i am using xldown. There are no blanks and the last row will differ based on a previous filter.

 

[Joe4]

That code will select Range("A1") every time, no matter what the last row calculation is (as you aren't selecting any rows).

If you want to copy the range that starts in A1 and goes to the last cell in column A, and copies to to cell O14, then try:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LastRow).Copy Range("O14")

 

[KGEORGE13]

Dumb Mistake! Thank you!

Now I don't want to move all of this data to another sheet, so is there a way I can delete all of the 1 million excess rows without having to copy my data to another sheet?

 

[Joe4]

Now I don't want to move all of this data to another sheet, so is there a way I can delete all of the 1 million excess rows without having to copy my data to another sheet?

You cannot really delete the blank rows - there will always be over a million rows on any spreadsheet (open up a new workbook and hit CTRL-Down Arrow and see where it ends.
What you can control is which rows you copy.

If you want help with this, you are going to need to provide some more specific details regarding how exactly we can determine what range to copy.
What is the exact logic we need to apply?

 

[KGEORGE13]

There are 1000 orders on a sheet. i want to filter out the "pin" orders (usually around 150) and paste them on to another sheet in the workbook. Each "pin" is made out of a material. On the Pin sheet, I want to filter by material and be able to run the macro for each material. Basically going from 1000 orders to a table for each material showing pin orders for that material.