Solved: trying to copy and paste too much data!

[CatDaddy]

trying to copy only visible cells after filter on a data set of 120,000 lines +, i understand there is some kind of limitation of special cells (15,000 lines?) and im not sure how to copy and paste this in chunks


ActiveWorkbook.Worksheets(1).Activate
Range("A1").Activate
Set MNPRng = Selection.CurrentRegion
MNPRng.AutoFilter Field:=4, Criteria1:="EMAIL", Operator:=xlFilterValues

Set MNPRng = MNPRng.SpecialCells(xlCellTypeVisible)
MNPRng.Copy

ActiveWorkbook.Worksheets(2).Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues


i get a 1004 runtime error about the data range and the debug takes me to the MNPRng line...any ideas?

[TJCMicropile]

Have you considered using the Dictionary object from the Scripting library (you'll have to add a reference to the Microsoft Scripting Runtime library)? The Dictionary object does a great job handling massive quantities of data (there's a nice article here: http://www.kamath.com/tutorials/tut009_dictionary.asp).

You might try something like this:


Dim dctFiltered As Dictionary
Dim cl as Variant

Set dctFiltered = New Dictionary

ActiveWorkbook.Worksheets(1).Activate
Range("A1").Activate

Set MNPRng = Selection.CurrentRegion
MNPRng.AutoFilter Field:=4, Criteria1:="EMAIL", Operator:=xlFilterValues

For Each cl In MNPRng.SpecialCells(xlCellTypeVisible)
'loads the values from each visible cell into the dictionary object
dctFiltered.Add cl.Value
Next cl

ActiveWorkbook.Worksheets(2).Activate
'The Application.Transpose will allow the data to be displayed vertically -
'You can remove it if it becomes a problem.
Range("A1:A" & dctFiltered.Count).Value = Application.Transpose(dctFiltered.Keys)

'Free up memory
Set dctFiltered = Nothing

[CatDaddy]

error

.Add "Argument not optional" -->dctFiltered.Add

:(

[TJCMicropile]

Sorry about that - I didn't test the code before I posted it. Dictionaries are supposed to store (key, item) pairs, which allow them to be searched for a particular key in a very efficient manner. The key is like a word in a real dictionary and the item is like the definition that goes with that word.

Here, though, I'm just using the keys in the dictionary, so we can just put some kind of dummy value in for the item.


dctFiltered.Add cl.Value, 0


If that doesn't work, I recommend making use of the .Resize function of the Range object (instead of paste), a process of which I myself have only recently learned. Have a look at this thread, http://www.vbaexpress.com/forum/showthread.php?t=37362&highlight=resize

Especially where Mark (GTO) suggests,

ThisWorkbook.Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(2).Value _
= wks.Range("K49:K50").Value


Hope that helps...

[CatDaddy]

Sorry about that - I didn't test the code before I posted it. Dictionaries are supposed to store (key, item) pairs, which allow them to be searched for a particular key in a very efficient manner. The key is like a word in a real dictionary and the item is like the definition that goes with that word.

Here, though, I'm just using the keys in the dictionary, so we can just put some kind of dummy value in for the item.


dctFiltered.Add cl.Value, 0


If that doesn't work, I recommend making use of the .Resize function of the Range object (instead of paste), a process of which I myself have only recently learned. Have a look at this thread, http://www.vbaexpress.com/forum/showthread.php?t=37362&highlight=resize

Especially where Mark (GTO) suggests,

ThisWorkbook.Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(2).Value _
= wks.Range("K49:K50").Value


Hope that helps...

the dictionary worked but it had the same size problem...i dont understand what the resize function would do or how i would use it in this case? if it makes a difference i'm trying to copy whole rows

[Jan Karel Pieterse]

Your best bet is to FIRST filter the table on the filter column(s) so you have all (or most) of the visible rows together in one or a couple of areas. That way you circumvent the 8192 area limitation.

Since you have a lot of data I'd consider having a temporary sheet to which you copy/paste the visible cells. Then you can use code like this to pick up all values extremely fast:
Dim vValues as Variant
vValues=Worksheets("Temp").UsedRange.Value

TJMicropile: Adding the cells one-by-one to that dictionary object will inevitably be quite slow.

[CatDaddy]

Your best bet is to FIRST filter the table on the filter column(s) so you have all (or most) of the visible rows together in one or a couple of areas. That way you circumvent the 8192 area limitation.

Since you have a lot of data I'd consider having a temporary sheet to which you copy/paste the visible cells. Then you can use code like this to pick up all values extremely fast:
Dim vValues as Variant
vValues=Worksheets("Temp").UsedRange.Value

TJMicropile: Adding the cells one-by-one to that dictionary object will inevitably be quite slow.

the problem i am having is copy-pasting that data because it cannot seem to copy 100,000+ lines, and i did run the filter first...so copying the data to a temporary sheet is also not an option (size)

[Jan Karel Pieterse]

What happens if you do try the copy, do you get an error message?

NB: The special cells limit is not a particular number of rows, it is the number of non-contiguous ranges (max 8192 blocks of cells) that is causing the problem.

[CatDaddy]

What happens if you do try the copy, do you get an error message?

NB: The special cells limit is not a particular number of rows, it is the number of non-contiguous ranges (max 8192 blocks of cells) that is causing the problem.

so if i sorted it first and it was just one block of 120000 lines it would work?!? thats definitely doable

EDIT: tried above with a little manipulation with great success! genius! cheers

[Jan Karel Pieterse]

Excellent, glad you could get it to work!

[CatDaddy]

Excellent, glad you could get it to work!

:beerchug: