select nonblank cells, filter, and copy and paste excel vba

Just as the title says I'm trying to select all cells that are not blank in the first column then select the whole selection. So my macro loops through and counts rows int he column until it is not blank to find the selection. Filter. Remove duplicates. Then copy and paste to a new sheet. I'm getting a debug error and wa开发者_高级运维s wondering if someone could help me out with the code. This is what I have:

Sub sum()

Dim countRow As Integer

countRow = 2

Do Until IsEmpty(Cells(countRow, 1))
    countRow = countRow + 1
Loop

Selection.AutoFilter
ActiveCell.Select
ActiveSheet.Range(Cells(1, 1), Cells(7, countRow)).AutoFilter Field:=4, Criteria1:="=yes*", _
Operator:=xlAnd
countRow = 2

Do Until IsEmpty(Cells(countRow, 1))
    countRow = countRow + 1
Loop

Selection.AutoFilter
ActiveCell.Select
ActiveSheet.Range(Cells(1, 1), Cells(7, countRow)).Select

Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range(Cells(1, 1), Cells(7, countRow)).RemoveDuplicates Columns:=Array(1, 7), _
    Header:=xlYes
End Sub

ActiveSheet.Range(Cells(1, 1), Cells(7, countRow)).Select

should be

ActiveSheet.Range(Cells(1, 1), Cells(countRow, 7)).Select