IF statement when cell is not empty

Question

Could you please help me clear this code?

I'm trying to filter out certain dates, and if no cells are visible then don't execute the code.

For some reason I cannot get the IF working properly.

Dim EndDate1, StartDate1 As Long
Dim todayDate1 As Long

Sheets("Macro").Select
todayDate1 = Sheets("Macro").Cells(2, 2)

Sheets("FTC").Select
StartDate1 = DateSerial(Year(todayDate1),Month(todayDate1),Day(todayDate1) + 14)
EndDate1 = DateSerial(Year(todayDate1), Month(todayDate1), Day(todayDate1) + 21)

ActiveSheet.Range("$A$1:$AP$1000").AutoFilter Field:=24, _
    Criteria1:=">=" & StartDate1, Operator:=xlAnd, Criteria2:="<" & EndDate1
Range("A2:A1000").Select
Selection.SpecialCells(xlCellTypeVisible).Select

If Selection.SpecialCells(xlCellTypeVisible).Count > 1 Then
    MsgBox "Nope"
End If
MsgBox "Yay"

Answer 1

There are a number of minor issues with your code, but your question was well-written and the effort you made is on the right track (and we all have to start somewhere) so kudos on that!

---

I think this is what you're trying to do:

Sub FilterDemo()

    Const filterField = 24
    Dim ws As Worksheet: Set ws = Sheets("Macro")
    Dim filterRange As Range: Set filterRange = ws.Range("A:AP")
    Dim dtStart As Date, dtEnd As Date, visibleRows As Long

    'assuming you want to use "today's date"? (returned by function [Date])
    dtStart = Date + 14
    dtEnd = Date + 21

    filterRange.AutoFilter Field:=1 'clear existing filter

    filterRange.AutoFilter Field:=filterField, _
        Criteria1:=">=" & Format(dtStart, "yyyy-MM-dd"), _
        Criteria2:="<=" & Format(dtEnd, "yyyy-MM-dd")

    visibleRows=ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible) _
            .Cells.Count - 1

    If visibleRows = 0 Then
        filterRange.AutoFilter Field:=1  'remove filter
        MsgBox "No data found between " & dtStart & " and " & dtEnd & ".", _
            vbExclamation, "No Data Found"
    Else
        MsgBox visibleRows &" items between" & dtStart &" and "& dtEnd & ".", _
            vbInformation, "Data found!"
    End If

End Sub

---

Notes:

• Great that you declared your variables - but better to declare dates as data type Date. Excel does store dates internally as numbers (although not integers) but it's still best to declare the variables as Date and let Excel figure out the rest.

  Since we're using the proper Date date type, we can simply add days to each other (instead of using DateSerial). (More about dates.)

• You declared StartDate1 As Long but not EndDate1: you can declare multiple variables on one line, but if any don't have a type specified then they default to Variant.

• I assume cell B2 just holds the current date? — if so, instead use the VBA the Date function.

• When passing dates as a parameter, it's a good idea to Format them in a way we know AutoFilter will understand., which also converts to String.

  It would probably also work fine like Criteria1:=">=" & dtStart, Criteria2:="<=" & dtEnd but the right way to do it is to convert to a string, since that's what AutoFilters' criteria areexpecting.

• Select statements should be avoided if possible (and should be "tidied" when using code from a recorded a macro). For example, this:

  Range("A2:A1000").Select
  Selection.SpecialCells(xlCellTypeVisible).Select
  If Selection.SpecialCells(xlCellTypeVisible).Count > 1 Then
  

  ...becomes this:

  If Range("A2:A1000").SpecialCells(xlCellTypeVisible).Count > 1 Then
  

  Important related reading: How to avoid using Select in Excel VBA

• By specifying range A1:AP1000, the Count Property will count all the rows up to 1000, only deducting the number of rows hidden by the filter.

  Instead of specifying an arbitrary set of rows, I refer to entire columns. AutoFilter "creates" a range of the cells it's using, and we can refer to like this expression: (borrowed from here)

  ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
  

---

When trying to figure out a long expression like this, it's helpful to read it right-to-left (or, for illustrative purposes, upside-down!)

---

• Next, either your operators or mine are incorrect in the AutoFilter criteria, depending on whether the values in your dates column have times attached.

  • Your code sample uses >=StartDate and <EndDate

    Yours excludes values of midnight on the EndDate. But if there are no times attached then yours is excluding "the whole day" of the EndDate. If there are times attached, then yours includes up to 11:59:59pm on the day before the EndDate.

  • My code example uses >=StartDate and <=EndDate.

    Mine includes values of midnight on the EndDate, and if there are no times attached to the data, the whole of EndDate is included. Yours is the correct way for use when querying time multiple date ranges, to prevent overlaps. (More about date/times.)

• Finally (mainly cosmetic), note that your MsgBox "Yay" will appear regardless of whether the MsgBox "Nope" does or doesn't appear.

  This is avoided with Exit Sub (to end the procedure immediately), or by using an Else with your If statement. (More about If..Then..Else.)

Hopefully this makes sense... Good luck!