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 (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 (https://www.youtube.com/@alanelston2330/featured)

[xander1981]

Attached as Zip406

[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.Co lumns(20), dDate) Then
rngFilter.AutoFilter field:=20, Criteria1:=dDate
Else
MsgBox "No delivery for today!", vbOKOnly
End If

[xander1981]

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



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.Co lumns(20), dDate) Then
rngFilter.AutoFilter field:=20, Criteria1:=dDate
Else
MsgBox "No delivery for today!", vbOKOnly
End If

End Sub

[Admin]

Hi

Here is screenshot of what I got.

407

[Excel Fox]

Try this one attached. Code below....

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


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

[xander1981]

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?



Sub todaysdels()

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

dDate = Date

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

End Sub

[xander1981]

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