0

I've been having some issues trying to clear a filter that I installed.

It's purpous is to show all the data inside the data base if a certain range of cells are empty.

I got it to a point where if I use a single cell as reference it will use the code as intended, but if I try a range with multiple cells it will always ignore the first part of the if statement

Dim rgData As Range, rgCriteria As Range

Worksheets("Pipeline").Activate

If IsEmpty(Range("G2:K2")) = True Then
    
    With ActiveSheet
    If .FilterMode Then
        ActiveSheet.ShowAllData
    End If
    End With

Else

    Set rgData = ThisWorkbook.Worksheets("Pipeline").Range("A4").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("Pipeline").Range("G1").CurrentRegion

    rgData.AdvancedFilter xlFilterInPlace, rgCriteria  

End If

End Sub

Am I declaring the range incorrectly?

Ek.murg
  • 1
  • 1
  • You simply can't use `isEmpty` on a Range. See for example https://stackoverflow.com/a/69402875/7599798 – FunThomas Dec 03 '21 at 12:50
  • Here you can find a way to check if a whole range is empty: https://stackoverflow.com/questions/10811121/detect-if-range-is-empty – FunThomas Dec 03 '21 at 12:52
  • 2
    `IsEmpty` can be used successfully on one cell only. Depending on your data, best use either `If Application.CountBlank(rg) = rg.Cells.Count Then` or `If Application.CountA(rg) = 0 Then`. – VBasic2008 Dec 03 '21 at 12:54
  • Perfect!!!!! It's working as intended now. Didn't know you couldn't use IsEmpty function with an array, thanks for all the help!! – Ek.murg Dec 03 '21 at 13:05

0 Answers0