VBA efficient copy of unique values for large data set

[robertgama]

I have a large data set with many complex formulas, and I started running out of resources getting errors.

I've created a VBA procedure that will import a new data file, set up the appropriate formatting, named ranges, etc. The rest of the spreadsheet updates automatically using many VLOOKUP, SUMIFS, INDEX/MATCH, SUMPRODUCT, and other array formulas.

I was going to use formulas to extract the unique values so the end user doesn't have to do an advanced filter, copy unique values to a new location but I ran out of memory before the formula could execute once so I realized I'll have to do this with VBA also.

My question is then what is the most efficient way to copy unique values to a new location?

I'm assuming that using advanced filters within VBA is probably the most efficient, but I was hoping someone could confirm that for me and perhaps post a code example.

There could be ~50,000 rows of data, and about 10,000 unique values that then have several columns of complex formulas.

Perhaps general suggestions for other ways to improve the efficiency would be appreciated also.

Thanks a bunch,

Rob.

 

[robertgama]

...I should also add that I've turned off automatic calculations, screen updating and user control.

 

[Dryver14]

I just recorded this and am quite impressed at how small the coding is

I copied to another location on the sheet but worth a go to move it to another page.

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("A1").Select
    Selection.AutoFilter
    Range("A1:H564").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "L1:S1"), Unique:=True
End Sub

 

[robertgama]

Thanks. I too just thought of checking out the recorded VBA and you're right, it doesn't look too bad.

    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Application.Goto Reference:="R2C1"
    Sheets("Data").Columns("B:B").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("A1"), Unique:=True

It was pretty quick too. Is this the most efficient method?

 

[Dryver14]

I would say it is the best method it certainly is in normal operations.

Advanced filter - unique - put it there.

 

[robertgama]

Thanks Dryver14.

Sometimes I've been getting too fancy with the formulas when VBA is probably the best way to go.

If I wanted to replace a bunch of other complex formulas (Ex. SUMIFS & other array formulas), is it most efficient to use Application.worksheetfunction and try to recreate the formulas or is it usually faster to do an alternate VBA approach?

This one's not that complex, but I'm just curious if it would be much more efficient if done in VBA and if so, should I use the Application.Worksheetfunction ?
Ex.
=COUNTIFS('Data Summary'!$B:$B,'Executive Summary'!$A5,'Data Summary'!K:K,"=1",'Data Summary'!$E:$E,">="&startDate,'Data Summary'!$E:$E,"<="&endDate)

Cheers.

 

[Dryver14]

I dont think worksheet functions work any faster in VBA than they do on the sheet, Coutifs works for me but there are some gurus on here that may know a better function, possibly sumproduct which, given the amount of functions you are doing may be more beneficial. I notice you are also summing full columns, with more than a million rows anything you can shave off that will help too

100000 is less than 10% of the sheet and you stated earlier you have about 50000 rows

 

[robertgama]

Thanks. I only did the entire columns since I don't know how many rows the data set is going to include.

I tried using COUNTA with INDIRECT or OFFSET, but then I started wondering if I was just going to slow the sheet down more by adding the extra complexity.

Maybe if I got the vba to append the max row into the formulas I could speed it up a bit without the extra overhead of the additional functions?

Thanks again