Copying unique values

[Jasminder Dhaliwal]

1. I have a table with anything from 1 to 250 rows.

2. I want to Autofilter Column H so it will only copy
unique values. I then want to copy columns H, J and N to a
new sheet.

3. Also there is a title row in row 1 of the sheet which I
dont want to be copied.

4. The destination sheet is "AGTRN"

Thank you so much for your help.

 

[Bernie Deitrick]

Jasminder,

Try the macro below. My assumption is that your data starts in row 1, and
the sheet with the data is the activesheet.

HTH,
Bernie
MS Excel MVP

Sub ExtractUniqueValues()
Dim mySheet As Worksheet
Set mySheet = ActiveSheet
Range("H1:H" & Range("H65536").End(xlUp).Row) _
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("H1:N" & Range("H65536").End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible).Copy
Sheets("AGTRN").Select
Range("A1").Select
ActiveSheet.Paste
Range("B1,D1:F1").EntireColumn.Delete
Range("A1").EntireRow.Delete
mySheet.ShowAllData
End Sub