Adjust code to give MsgBox if no data

LNG2013

Active Member
Joined
May 23, 2011
Messages
428
How could I adjust the below code to popup a message box if there is now data on sheet filtered

Code:
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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.

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

LNG2013

Active Member
Joined
May 23, 2011
Messages
428
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?

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

LNG2013

Active Member
Joined
May 23, 2011
Messages
428
The odd thing is I do get the popup box when there is data in filtered!
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
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.

Code:
Sheets("Sheet1").select

You can try using.

Code:
application.displayalerts=false

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

Code:
application.displayalerts=true
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top