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("A2:BF" & LastRow & "".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("D".Cut
Columns("A:A".Insert Shift:=xlToRight
Columns("J:J".Cut
Columns("B:B".Insert Shift:=xlToRight
Columns("K:K".Cut
Columns("C:C".Insert Shift:=xlToRight
Columns("AP:AP".Cut
Columns("D".Insert Shift:=xlToRight
Columns("W:W".Cut
Columns("E:E".Insert Shift:=xlToRight
Columns("F:BF".Select '<<< AU IS THE LAST VALID COL (BE IS FINAL TEMP COLUMN)
Selection.EntireColumn.Hidden = True
Columns("A:E".Select
Range("A1:E" & LastRow & "".AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("F:BF".Select
Selection.EntireColumn.Hidden = False
' ##########################################################
'COPY THE CURRENT DATA SHEET AND PASTE INTO A NEW SHEET
'(none of these work)
'Selection.Cut
'Range("A1".CurrentRegion.Cut
'Range("A1:BF" & LastRow & "".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("A:A".Cut '<<< SAMPLE
Columns("E:E".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< METHOD
Columns("N:N".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< EXTMETHOD
Columns("N:N".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< PREPREPMETHOD
Columns("AP:AP".Insert Shift:=xlToRight
Columns("B:B".Cut '<<< PARAMID
Columns("W:W".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< MUST MOVE SAMPLE AGAIN
Columns("E:E".Insert Shift:=xlToRight
'NOW PASTE THE NEW DATA INTO SHEET 1, WHICH CONTAINS THE ENCODED HEADER
Range("A2:BF" & LastRow & "".Cut
Sheets(1).Activate
Cells(2, 1).Select
ActiveSheet.Paste
Set gwsDSheet = Nothing
Application.DisplayAlerts = False
Sheets(2).Delete
Application.DisplayAlerts = True
End Sub
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("A2:BF" & LastRow & "".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("D".Cut
Columns("A:A".Insert Shift:=xlToRight
Columns("J:J".Cut
Columns("B:B".Insert Shift:=xlToRight
Columns("K:K".Cut
Columns("C:C".Insert Shift:=xlToRight
Columns("AP:AP".Cut
Columns("D".Insert Shift:=xlToRight
Columns("W:W".Cut
Columns("E:E".Insert Shift:=xlToRight
Columns("F:BF".Select '<<< AU IS THE LAST VALID COL (BE IS FINAL TEMP COLUMN)
Selection.EntireColumn.Hidden = True
Columns("A:E".Select
Range("A1:E" & LastRow & "".AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("F:BF".Select
Selection.EntireColumn.Hidden = False
' ##########################################################
'COPY THE CURRENT DATA SHEET AND PASTE INTO A NEW SHEET
'(none of these work)
'Selection.Cut
'Range("A1".CurrentRegion.Cut
'Range("A1:BF" & LastRow & "".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("A:A".Cut '<<< SAMPLE
Columns("E:E".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< METHOD
Columns("N:N".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< EXTMETHOD
Columns("N:N".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< PREPREPMETHOD
Columns("AP:AP".Insert Shift:=xlToRight
Columns("B:B".Cut '<<< PARAMID
Columns("W:W".Insert Shift:=xlToRight
Columns("A:A".Cut '<<< MUST MOVE SAMPLE AGAIN
Columns("E:E".Insert Shift:=xlToRight
'NOW PASTE THE NEW DATA INTO SHEET 1, WHICH CONTAINS THE ENCODED HEADER
Range("A2:BF" & LastRow & "".Cut
Sheets(1).Activate
Cells(2, 1).Select
ActiveSheet.Paste
Set gwsDSheet = Nothing
Application.DisplayAlerts = False
Sheets(2).Delete
Application.DisplayAlerts = True
End Sub