Adjust code to give MsgBox if no data

[LNG2013]

How could I adjust the below code to popup a message box if there is now data on sheet filtered

Sub DistributeRows()
Application.ScreenUpdating = False
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim Lastrow As Long
Dim LastRowCrit As Long
Dim i As Long
    
    Set wsAll = Worksheets("Filtered") ' change All to the name of the worksheet the existing data is on
    
    Lastrow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsCrit = Worksheets.Add
    
    ' column AA has the criteria eg project ref
    wsAll.Range("AA1:AA" & Lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = "Week" & wsCrit.Range("A2")
        wsAll.Rows("1:" & Lastrow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next i
    
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

 

[Jaye7]

You can place the following before the end sub and if there is information in A1 a messagebox will pop up.

You can change the range and the message to what you like.

If Range("A1").Value <> "" Then
    MsgBox "New Data Available"
    End If

 

[LNG2013]

Hey Jaye

I actually tried placing this towards the top of the sub and added a select to target the sheet the data would be on... I did this mainly because this code creates sheets as it goes and I didn't want to confuse it.

For some reason when there is still no data, I do not get the msg box for this particular set of code all together.

I also get an error with my previous code that there is no data, is there a way to supress this error?

Sheets("Filtered").Select
    If Range("A2").Value <> "" Then
    MsgBox "No data available for this Date/Goal range."
    End If

 

[LNG2013]

The odd thing is I do get the popup box when there is data in filtered!

 

[Jaye7]

I think that you should use it at the end of the sub as it would then check the specific sheet that has the data, that way if it doesn't have the data the messagebox will popup, using it before your code finishs means that it pops up early and that can interfere with your code as it waits for you to click Ok before completing execution.

As long as you specify the sheet before my script it won't get confused.

Sheets("Sheet1").select

You can try using.

application.displayalerts=false

but you must reset it to true at the end of the sub

application.displayalerts=true