Do Not AutoFilter If Data Does Not Exist

Hello ExcelFox Forum users. My first post, how exciting.

OK well I am a beginner i'm afraid so I hope I don't dumb down the water of this site too much :)

I don't work with code but at work I have created many spread sheets in order to keep a record of international shipments coming into the UK.
I have written many Macros and formulas on these spreadsheets but Im just having one problem I cant seem to get on top of. I would be very grateful if you kind people would help me.

My spreadsheet has a column which is shows a date. This date is when the container delivered to the customer in UK. I run a macro triggered by a button which filters the column to the current date. Therefore when run on the date of delivery is shows "Deliveries for today" This is fine but when there are no deliveries that day i.e. today's date is not in any cell in this specific column then it filters and nothing is displayed. Because the spreadsheet goes out to the customer I think this looks a little messy. Instead, if there are no deliveries on the day I would like the 'msgbox' to pop up and say "there are no deliveries today" instead of just filtering so it just shows empty, non active rows. I have tries many bits of code over the last few weeks but can't get any to work.

My code for the filter is below. I need perhaps an 'If' statement so if after filter this is no active rows then msgbox "No deliveries today"

Code:

---

Sub todaysdels()

    dDate = Date
   
    Range("A2").AutoFilter field:=20, Criteria1:=dDate
   
End Sub

---

I hope I have explained correctly. Thanks.
Xander1981

Hi Xander1981,

Welcome to ExcelFox !!!

try something like

Code:

---

If Application.WorksheetFunction.CountIf(Range("T:T"), dDate) Then
    Range("A2").AutoFilter field:=20, Criteria1:=dDate
End If

---

BTW, add code tags while posting code on the forum. This time I added for you. :)

You can modify Admin's code to even give that message you wanted

Code:

---

If Application.WorksheetFunction.CountIf(Range("T:T"), dDate) Then
    Range("A2").AutoFilter field:=20, Criteria1:=dDate
Else
    MsgBox "No delivery for today!", vbOkOnly
 End If

---

https://www.youtube.com/@alanelston2330/featured

Thanks both for your very quick responses but this doesn't seem to work. I just changed it to work on column 'U' which displays the date and time so the cell is customs format dd/mm/yyy hh:mm I wonder if this is the problem, but I tried with column 'S' also and still didn't work. I put in today's date to see if it would filter to that row but it didn't and just came up with the msgbox. :(

Sorry to be such a beginner.



https://www.youtube.com/@alanelston2330/featured

If

dDate and the date you are filtering for (today?) is exactly the same, then it shouldn't be a problem. If the problems still persists, can you attach a sample file.

Thanks Excel Fox, yes I see your point. dDate (today's date) and the column I am filtering are the same so I don't know what i'm doing wrong. I think I have missed something silly.

Code:

---

Sub todaysdels()

    dDate = Date
   
    If Application.WorksheetFunction.CountIf(Range("U3:U500"), dDate) Then
    Range("A2").AutoFilter field:=20, Criteria1:=dDate
Else
    MsgBox "No delivery for today!", vbOKOnly
 End If
   
End Sub

---

I tried attaching my file but the size (849 Kb) exceeds the formum limit for xls files.


https://www.youtube.com/@alanelston2330/featured

Attached as ZipAttachment 406

Hi

Replace the range U3:U500 with T3:T500

Thanks Admin, This did filter but showed no rows even though today's date is in row 4. Thanks for your patients.

Hi

It works fine here.

Code:

---

Dim rngFilter  As Range
Dim dDate      As Date

dDate = Date

Set rngFilter = Range("a2:z" & Range("a" & Rows.Count).End(xlUp).Row)

If Application.WorksheetFunction.CountIf(rngFilter.Columns(20), dDate) Then
    rngFilter.AutoFilter field:=20, Criteria1:=dDate
Else
    MsgBox "No delivery for today!", vbOKOnly
End If

---

Thanks very much Admin. I am running the code exactly as below with today's date in column 'T' but when filtered column 'T' is not shown :(

Code:

---

Sub todaysdels()

 Dim rngFilter  As Range
Dim dDate      As Date

dDate = Date

Set rngFilter = Range("a2:z" & Range("a" & Rows.Count).End(xlUp).Row)

If Application.WorksheetFunction.CountIf(rngFilter.Columns(20), dDate) Then
    rngFilter.AutoFilter field:=20, Criteria1:=dDate
Else
    MsgBox "No delivery for today!", vbOKOnly
End If
   
End Sub

---

Hi

Here is screenshot of what I got.

Attachment 407

Try this one attached. Code below....

I have changed the formula used in Column T. This is working at my side....

Code:

---

Option Explicit

Sub FilterToday()

    FilterForDate Date
   
End Sub

Sub FilterTomorrow()

    FilterForDate CDate(Date + 1)
   
End Sub
Sub FilterForDate(dDate As Date)
   
    With Worksheets("test")
        .Range("A2").AutoFilter field:=20, Criteria1:=dDate
        If .Range("T3:T1000").Find(dDate, , xlValues) Is Nothing Then
            MsgBox "No delivery for today!", vbOKOnly
        End If
    End With
   
End Sub

---

Thanks Both for your help. The strangest thing, I pasted the code into the version I use at work and the sheet works fine. Only problem is I have 4 sheets for 4 different customers. Much the same but some solumn info different. I tried to use the same code to filter todays deliveries on another sheet and it doesnt work. The delivery date it in column 16 this time so I changed the code but nothing shows once filtered. Any ideas where I am going wrong?

Code:

---

Sub todaysdels()

Set rngFilter = Range("a2:P400" & Range("a" & Rows.Count).End(xlUp).Row)

dDate = Date

If Application.WorksheetFunction.CountIf(rngFilter.Columns(16), dDate) Then
    rngFilter.AutoFilter field:=16, Criteria1:=dDate
Else
    MsgBox "There are no delivery for today!", vbOKOnly
End If
 
End Sub

---

Thanks All. Now works brilliant! The code was perfect it was the cell formate giving me trouble :) Cheers