Bypass Clipboard when copy/pasting non-contiguous rows

**mlj61289** · 03-21-2014, 02:37 PM

Hello Everyone-

I think a pretty common thing to do with vba is to autofilter some range, and then copy/paste the visible cells to another range. I've got that down, and that is currently what I'm doing. The problem is that my data set can grow to 400k+ rows of data with 40 columns, and using the clipboard to perform this task, while quick enough, takes huge amounts of memory. The code stops running with an "out of memory" error and I dont know of a way to clear the memory in between steps. So, is there a way to do this by bypassing the clipboard all together?

Here is a sample snippet of code that comes close to what I'm trying to do, but only moves over the first block of contiguous rows, and gives me #N/A's for the rest. Is there a way to do this?

As always, thanks in advance for any help!!

P.S. - this would assume that the user has set the autofilter on their own.

Please Login or Register  to view this content.

**daffodil11** · 03-21-2014, 03:11 PM

I ran into a very similar issue on my last big project, which was 294k rows by 44 Columns.

Ultimately, my workaround was to run a For Each loop and copy the entire row of each that met my filtering criteria to the next row of another tab.

Here's the piece of code with the copy loop/filter:

Please Login or Register  to view this content.

My automated filter pulled latitude/longitude cells of each row, compared it to another sheet using some fancy trigometry to calculate distance, made sure a few column contained some values and didn't include others, and then copied the row over. Running the full trig macro on every line takes me about 22 seconds.

We use this as a tool now to query datasets for our analysts to build all kinds of studies, usually depending on each record being within x miles of a point.

Would this kind of process help you?