Run macro only if condition is met.

[eurano]

Hi,
I have following macro wich run 2 macros but I want to check if condition is met before this macro even start. In spreadsheet named "Arkusz1" i have pivot table named "Tabela przestawna 2" wich when its empty macro shouldnt start and show msgbox "there is nothing to send". In addition this pivot table is allways filtered.

Sub RunThemAll()

    Application.Run "vbaAutoRefreshPivot2"
    
    Application.Run "Notes_Email_Excel_Cells"
    
   
End Sub

Picture of pivot below:

 

[WarPigl3t]

Well first I need to know the range of data that we are checking to see if its empty. So in this macro, I'll assume that the range of data that might be empty is B2:C25. Because I'm going to assume column A is never empty. You would run the Sub checkIfRangeHasData().

Sub checkIfRangeHasData()
     dataRange = "B2:C25"
     If emptyCheck(dataRange) = "Empty" Then
          MsgBox "There is nothing to send"
     Else
          Call RunThemAll()
     End If
End Sub

Function emptyCheck(dataRange)
     If WorksheetFunction.CountA(Range(dataRange)) = 0 Then
          emptyCheck = "Empty"
     Else
         emptyCheck = "Not Empty"
     End If
End Function

Sub RunThemAll()
     Application.Run "vbaAutoRefreshPivot2"
     Application.Run "Notes_Email_Excel_Cells"
End Sub

 

[eurano]

The pivot table range is named "Report" (i defined range as dynamic value so it resize whenever there will be more rows in pivot table).

 

[WarPigl3t]

You didn't understand the problem so just post an example table so I can figure it out myself.

 

[eurano]

The range to check is pivot table in cell N1. If there is no data there like in the picture from first post then macro shouldnt start,

 

[WarPigl3t]

Sub checkIfRangeHasData()
     '65536 rows in Excel 1997
     '1048576 rows in Excel 2007, 2010,and  2013
     dataRange = "N2:S1048576"
     If emptyCheck(dataRange) = "Empty" Then
          MsgBox "There is nothing to send"
     Else
          Call RunThemAll()
     End If
End Sub

Function emptyCheck(dataRange)
     If WorksheetFunction.CountA(Range(dataRange)) = 0 Then
          emptyCheck = "Empty"
     Else
         emptyCheck = "Not Empty"
     End If
End Function

Sub RunThemAll()
     Application.Run "vbaAutoRefreshPivot2"
     Application.Run "Notes_Email_Excel_Cells"
End Sub

 

[WarPigl3t]

And just to clarify, N1 is not a range so your last post didn't make sense and I had to assume you meant the range started at N1. The range is N1:S1048576. In the code I started with N2 because of the headers that will never be blank.

 

[WarPigl3t]

Hope it works out for you. I'm unsubscribing from this thread.

 

[eurano]

Hi, it doesnt work e-mail message is still sending.

 

[WarPigl3t]

forgot Dim dataRange As Range