CODEWITHSUNDEEP
vbaexceladvanced-filter
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

AdvancedFilter xlFilterCopy returns empty cells also

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.

Upvotes: 1

Views: 1392

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 2

Related Questions