select unique records based on multiple columns

[mpopnoe]

What I'm trying to do is find unique records based on a compound unique key, that being 5 columns for this example. In order to do the filter, I place the columns in columns 1-5, hide the remaining columns and filter. That part works fine, the only problem is when I try to select just the filtered rows, I'm selecting all the rows so when I paste the data to another sheet it is all the records and not just the unique filtered ones. The code is below, the portion surrounded by ## is where I'm trying to select just the filtered rows.

Thanks in advance.

Sub Filter_Unique()
'CREATE 2 COPIES OF THE DATA SHEET: 1 TO FILTER ON, 1 TO BE THE FINAL
gwsDSheet.Copy before:=Sheets(1) '<<< SHEET 1
Range(&quot;A2:BF&quot; & LastRow & &quot;&quot.Delete '<<< DELETE ALL BUT THE HEADER
gwsDSheet.Copy after:=Sheets(1) '<<< SHEET 2

'COLUMNS TO FILTER MUST BE CONSECUTIVE AND START FROM 1ST COLUMN
'PLACE SAMPLE,METHOD,EXTMETHOD,PREPREMETHOD AND PARAMID IN 1ST 5 COLUMNS
Columns(&quot;D&quot.Cut
Columns(&quot;A:A&quot.Insert Shift:=xlToRight
Columns(&quot;J:J&quot.Cut
Columns(&quot;B:B&quot.Insert Shift:=xlToRight
Columns(&quot;K:K&quot.Cut
Columns(&quot;C:C&quot.Insert Shift:=xlToRight
Columns(&quot;AP:AP&quot.Cut
Columns(&quot;D&quot.Insert Shift:=xlToRight
Columns(&quot;W:W&quot.Cut
Columns(&quot;E:E&quot.Insert Shift:=xlToRight

Columns(&quot;F:BF&quot.Select '<<< AU IS THE LAST VALID COL (BE IS FINAL TEMP COLUMN)
Selection.EntireColumn.Hidden = True

Columns(&quot;A:E&quot.Select
Range(&quot;A1:E&quot; & LastRow & &quot;&quot.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Columns(&quot;F:BF&quot.Select
Selection.EntireColumn.Hidden = False
' ##########################################################
'COPY THE CURRENT DATA SHEET AND PASTE INTO A NEW SHEET
'(none of these work)
'Selection.Cut
'Range(&quot;A1&quot.CurrentRegion.Cut
'Range(&quot;A1:BF&quot; & LastRow & &quot;&quot.Cut
'###########################################################
gwsDSheet.Activate
Cells(1, 1).Select
ActiveSheet.Paste

Application.DisplayAlerts = False
Sheets(2).Delete
Application.DisplayAlerts = True

'MOVE THE COLUMNS BACK INTO ORDER
Columns(&quot;A:A&quot.Cut '<<< SAMPLE
Columns(&quot;E:E&quot.Insert Shift:=xlToRight
Columns(&quot;A:A&quot.Cut '<<< METHOD
Columns(&quot;N:N&quot.Insert Shift:=xlToRight
Columns(&quot;A:A&quot.Cut '<<< EXTMETHOD
Columns(&quot;N:N&quot.Insert Shift:=xlToRight
Columns(&quot;A:A&quot.Cut '<<< PREPREPMETHOD
Columns(&quot;AP:AP&quot.Insert Shift:=xlToRight
Columns(&quot;B:B&quot.Cut '<<< PARAMID
Columns(&quot;W:W&quot.Insert Shift:=xlToRight
Columns(&quot;A:A&quot.Cut '<<< MUST MOVE SAMPLE AGAIN
Columns(&quot;E:E&quot.Insert Shift:=xlToRight

'NOW PASTE THE NEW DATA INTO SHEET 1, WHICH CONTAINS THE ENCODED HEADER
Range(&quot;A2:BF&quot; & LastRow & &quot;&quot.Cut
Sheets(1).Activate
Cells(2, 1).Select
ActiveSheet.Paste

Set gwsDSheet = Nothing
Application.DisplayAlerts = False
Sheets(2).Delete
Application.DisplayAlerts = True

End Sub

 

[DaleWatson123321]

Hi mpopnone,

The solution to your task is really quite simple.

Excel's &quot;Advanced Filter&quot; is much more powerful than most Excel users realize.

The best method of me demonstrating this power, would be for you to email me a brief example of your file. Just include the field headings, a small number of records, and specify the criteria for the records you want to isolate.

I'll then modify the file and return it.

Hope this helps.

Regards, ...Dale Watson nd.watson@shaw.ca

 

[TonyJollans]

Hi mpopnoe,

Somewhat bizarrely, the Copy and Delete Methods work only on the visible rows, but the Cut method works on visible AND hidden rows. I have no idea why, but if you use Copy and Paste, followed by Delete you should get the result you want. It's rather a shame that the filter won't just copy to a new sheet for you.

However, the code seems somewhat complex. I haven't studied it in detail at the moment and may be missing something obvious, but what is the purpose of hiding and reordering and whatever else you are doing to all the columns? I'll have a proper look at it tomorrow.

Enjoy,
Tony

 

[mpopnoe]

Thanks to both of you.

Tony,
The reason I move the columns around is because from my limited experience, the only way to create a compound unique key (of sorts) in Excel to filter on is to place them inline, then hide the columns that are not part of the unique key. Is this way off? I started out as a VB programmer before ever learning Excel so I fall into doing things the hard way quite often.

Dale,
I'll send you a file to work with. I'm creating client reports for lab data (I'll have to send you dummy data).

Thanks guys!

 

[Tiglet]

Try this:

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste[/blue]

and if you want to remove the data from the original worksheet:


Selection.SpecialCells(xlCellTypeVisible).Select
Selection.ClearContents[/blue]

The (xlCellTypeVisible).Select [/blue]command is the code generated by doing the following:

Edit, GoTo, click Special, select visible cells only.




Tiglet

[blue]Living on Earth is expensive, but it does include a free trip around the sun every year.[/blue]

 

[mpopnoe]

Tiglet,
Thanks! That worked great, except I did need to add this line to select the valid range of data otherwise &quot;all&quot; visible cells, all the way down to the maximum rows allowed in Excel (65k+) are copied into the clipboard and the virtual money is maxed.

Range(Cells(1, Range(&quot;Sample&quot.Column), Cells.SpecialCells(xlCellTypeLastCell)).Select

Thanks to everyone for your quick and excellent responses!