Do Not AutoFilter If Data Does Not Exist

[xander1981]

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"

Sub todaysdels()

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

I hope I have explained correctly. Thanks.
Xander1981

[Admin]

Hi Xander1981,

Welcome to ExcelFox !!!

try something like

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.

[Excel Fox]

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

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

[xander1981]

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

[Excel Fox]

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.

[xander1981]

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.

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

[xander1981]

Attached as ZipAttachment 406

[Admin]

Hi

Replace the range U3:U500 with T3:T500

[xander1981]

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

[Admin]

Hi

It works fine here.

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