Detect if a range is empty after filtering

Asked Aug 01 '19 at 19:40

Active Aug 01 '19 at 19:42

Viewed 319 times

So I am writing a code that after I have filtered on certain criteria I want to select the data (excluding the headers) and if the selection is not empty copy and paste in into a different sheet. If the selection is empty, do nothing. My problem is that it seems my code always thinks that the selection is not empty even when it is so it always copies the data.

Sub paste_filter()
    Sheets("RawData").Select
    Dim rng As Range
    With ActiveSheet.AutoFilter.Range.Offset(1, 0).Resize(Rows.Count - 1).Select
        Set rng = Selection
        If Application.WorksheetFunction.CountA(rng) > 0 Then
            rng.Copy
            Sheets("Report").Select
            Range("A5").Select
            Selection.End(xlDown).Select
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveSheet.Paste
        End If
    End With
End Sub

edited Aug 01 '19 at 19:42

JNevill

46,980
4
38
63

asked Aug 01 '19 at 19:40

Nadezhda Karkelanova

Use `Range.SpecialCells(xlCellTypeVisible)`. – BigBen Aug 01 '19 at 19:43
When I get to the if statement though it still thinks it is not empty – Nadezhda Karkelanova Aug 01 '19 at 19:45
See https://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table for an example. – BigBen Aug 01 '19 at 19:47

Detect if a range is empty after filtering

0 Answers0