IsEmpty Range vs. IsEmpty Cells

[dandelion]

I have a pivot table and a Column (AA) with Value next to the pivot table. I'm making a code that when I change the pivot table (e.g by selecting slicer): 1. If Range("AA11: AA5000") is not empty, a prompt message will ask whether want to clear the data in column AA? 2. But if Range("AA11: AA5000") is empty, no need to show the msgbox. However, when I write the code below, it does not work. I try to change Range to Cell("AA11") then it works. But I want range instead of Cells. Can you help me? TIA.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If IsEmpty(Range("AA11:AA50000")) = False Then
Dim answer As VbMsgBoxResult
answer = MsgBox("Do you want to clear the data?", vbYesNo, "Clear Data")
If answer = vbYes Then

Call ClearPreviousData
End If
End If

End Sub

 

[Jaafar Tribak]

A multiple cell range returns an array. IsEmpty doesn't work on arrays.
You could either iterate through each cell in the range and use IsEmpty on each individual cell or maybe use this simpler alternative function:

Function IsRangeEmpty(ByVal R As Range) As Boolean IsRangeEmpty = (WorksheetFunction.CountA(R) = 0) End Function

 

[dandelion]

A multiple cell range returns an array. IsEmpty doesn't work on arrays.
You could either iterate through each cell in the range and use IsEmpty on each individual cell or maybe use this simpler alternative function:

Function IsRangeEmpty(ByVal R As Range) As Boolean IsRangeEmpty = (WorksheetFunction.CountA(R) = 0) End Function

It works well Thank you so much

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: IsEmpty Range vs. IsEmpty Cells
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.