CODEWITHSUNDEEP
vbaexcel
doyz
doyz

Reputation: 886

VBA: Output message box when Autofilter returns no data

I want to copy and paste auto-filtered range to a new worksheet if there are any results after filtering, and show a message box if there is no result.

However, when I test using a filter criteria that would not return any results, the message box does not appear (blank worksheet shows)

    Dim WSNew As Worksheet
    Set WSNew = Worksheets.Add

    Dim rngVisible As Range
    Set rngVisible = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

    If rngVisible.Rows.Count > 1 Or rngVisible.Areas.Count > 1 Then
        rngVisible.Copy
            With WSNew.Range("A1")
                .PasteSpecial Paste:=8
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
                .Select
            End With
    Else
        MsgBox ("No such filtered criteria")
    End If

Upvotes: 1

Views: 2328

Answers (2)

Nikolaos Polygenis
Nikolaos Polygenis

Reputation: 691

Please check this:

Option Explicit
Sub Filter_range()


  Dim WSNew As Worksheet
  Dim rngVisible As Range




    Set rngVisible = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

    If rngVisible.Rows.Count > 1 Or rngVisible.Areas.Count > 1 Then
        rngVisible.Copy

        Set WSNew = Worksheets.Add

            With WSNew.Range("A1")
                .PasteSpecial Paste:=8
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
                .Select
            End With
    Else
        MsgBox ("No such filtered criteria")
    End If
End Sub

Upvotes: 1

Putxe
Putxe

Reputation: 1074

First you want to work in the active sheet but when you execute Worksheets.Add the worksheet added can become the active sheet (depends on Excel versions I think). That can be an issue. So you have to set a WSOld and work on it.

Moreover, your autofilter function is not in the right order (first declare the Worksheet.Range(firstColumfirstLine : lastColumLastLine) and then autofilter on it : https://msdn.microsoft.com/fr-fr/library/office/ff193884.aspx).

You have also to choose the criteria(s) to filter the data.

And then use the UsedRange.SpecialCells(xlCellTypeVisible) to set a range with the filtering cells and interact on it.

This works for me :

 Dim WSOld As Worksheet
 Dim WSNew As Worksheet

'store the active sheet in WSOld to be sure that selection will be apply on it
Set WSOld = ActiveSheet
Set WSNew = Worksheets.Add

'select the range to apply the filter and choose criteria
WSOld.Range("A1:B6500").AutoFilter Field:=2, Criteria1:="te"

'select the data visible after filter
Dim rngVisible As Range
Set rngVisible = WSOld.UsedRange.SpecialCells(xlCellTypeVisible)

If rngVisible.Rows.Count > 1 Or rngVisible.Areas.Count > 1 Then
    rngVisible.Copy
        With WSNew
            .Range("A1").PasteSpecial Paste:=8
            .Range("A1").PasteSpecial xlPasteValues
            .Range("A1").PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
            .Select
        End With
Else
    MsgBox ("No such filtered criteria")
End If

'remove autofilter
WSOld.Range("A1:B6500").AutoFilter

Hope it helps.

Upvotes: 1

Related Questions