AdvancedFilter xlFilterCopy returns empty cells also

Question

Hi I tried to implement advanced filter xFiltercopy. But the issue is it takes blank data also. So How can we avoid this issue.

Here is my code to filter

Columns("G:G").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z2"), Unique:=True

But on the pasted cells, it taking even the empty rows also. So I need to avoid empty text. My data looks like this now with empty rows also

Name1
Name2
Name3

Name4
Name5

Here as you can see there is a blank cell between name3 and name4. So how can we skip empty cells.

Answer 1

There is no provision for ignoring blanks in AdvancedFilter without providing a criteria range that states so. However, it is easy enough to identify and remove blank cells from the results.

With Worksheets("sheet10")
    .Range(.Cells(2, "K"), .Cells(.Rows.Count, "K").End(xlUp)).clearcontents
    .Columns("G:G").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("K2"), Unique:=True
    With .Range(.Cells(2, "K"), .Cells(.Rows.Count, "K").End(xlUp))
        If CBool(Application.CountBlank(.Cells)) Then
            .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
        End If
    End With
End With